REGEXEXTRACT multiple values - Google Sheets

Question

I have the following entries in ONE google sheets cell

[email protected]; [email protected]; [email protected]; [email protected]!; [email protected]; [email protected]!; [email protected];

Now I would like to create the following output (in one cell aswell) using formulas.

Expected Result:

[email protected]; [email protected]

I tried using REGEXEXTRACT but this function only return the first match ([email protected])

Any proposals?

Does this mean you want all addresses inside `ext!...!`, or all that consist only of letters for the identifiers, or both? Please add a description of what matches you are looking for to your question. — oriberu, Mar 17 '20 at 11:38
https://stackoverflow.com/questions/43432409/multiple-regex-matches-in-google-sheets-formula — MrCason, Sep 15 '22 at 17:09

player0 · Answer 1 · 2020-03-17T10:04:09.057

0

try:

=ARRAYFORMULA(TRIM(TEXTJOIN("; "; 1; IF(REGEXMATCH(
 SPLIT(A1; ";"); "[email protected]|[email protected]"); SPLIT(A1; ";"); ))))

edited Mar 17 '20 at 10:04

answered Mar 17 '20 at 09:45

player0

thanks I like that approach. However I would prefer a dynamic solution. My real data has way more lines with that format ([email protected]!). Moreover people will frequently add new lines. So I dont want the specific lines to be "hard coded" in the formula. Is this even feasible? – DocBenchpress Mar 17 '20 at 10:32
can you share a copy of your sheet with fake data and example of desired output? – player0 Mar 17 '20 at 10:45
@DocBenchpress can you modify your question in this case? So that it would not seem that this is a chameleon question – Jeff Rush Mar 17 '20 at 10:56
1

I would try to use this regex and modify as you wish `\w{2,3}\!\w{2,3}\@\w{2,3}\.\w{2,3}\!`, try it on [regexr.com](https://regexr.com/) – Jeff Rush Mar 17 '20 at 11:20

1 Answers1