-2

I have the following entries in ONE google sheets cell

12@xy.de; 123@xy.de; 1234@xy.de; ext!abc@def.de!; 321@xy.de; ext!def@abc.de!; 4321@xy.de;

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

Expected Result: 

abc@def.de; def@abc.de

I tried using REGEXEXTRACT but this function only return the first match (abc@def.de)

Any proposals?

player0
  • 69,261
  • 8
  • 33
  • 67
DocBenchpress
  • 31
  • 3
  • 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

1 Answers1

-1

try:

=ARRAYFORMULA(TRIM(TEXTJOIN("; "; 1; IF(REGEXMATCH(
 SPLIT(A1; ";"); "abc@def.de|def@abc.de"); SPLIT(A1; ";"); ))))

0

player0
  • 69,261
  • 8
  • 33
  • 67
  • thanks I like that approach. However I would prefer a dynamic solution. My real data has way more lines with that format (ext!y@aa.de!). 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
  • 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