How to extract multiple email addresses in a Google doc?

Question

This is the content of the cell and several like this. I need to extract only the email addresses.

This is the function that I have so far:

=arrayformula(regexextract(query(split( transpose(split(A1, ",")), ":" ),"select Col2 where Col1 contains 'email' ",0),"\w.+\w"))

I have more than one cell with the info when I applied this function I am not able to drag down the function.

Answer 1

Use split(), regexextract() and join(), like this:

=arrayformula( 
  join( 
    ", ", 
    regexextract( 
      query( 
        split( transpose(split(A1, ",")), ":" ), 
        "select Col2 where Col1 contains 'email' ", 
        0 
      ), 
      "\w.+\w" 
    ) 
  ) 
)

Answer 2

REGEXREPLACE & REGEXEXTRACT

=INDEX(TOCOL(LET(
   str,REGEXREPLACE(TOCOL(A:A,1), "[{\[\]}""]", "¥"),
   REGEXEXTRACT(str, REGEXREPLACE(
     str, "mail¥:¥(.*?)¥", "mail¥:¥($1)¥"))),1))

INDEX  LET  REGEXEXTRACT  REGEXREPLACE  TOCOL

1. str stores the strings after replacing any quotation marks ", brackets [], and braces {} with ¥, an arbitrarily chosen character that must meet the following two conditions:
   1. It won't be found in the source data
   2. It doesn't need to be escaped in regular expressions.
2. REGEXREPLACE inserts parentheses around any email addresses in str.
3. REGEXEXTRACT uses the string created in step #2 as a regular expression to extract the email addresses from str
4. The formula is wrapped in TOCOL to convert the rows of email addresses to a single column. It can optionally be excluded.