Looking for query to extract an email address from a column

Question

Using a query, I want to generate the Result table from the Data table.

Data
This address, [email protected], is great
[email protected] is a better email address
The best address is [email protected]

Result
[email protected]
[email protected]
[email protected]

I've seen regexextract to get the email address (as in the following line), but I would like to include that or some code like it within the query and am not sure how to do it.

=iferror(Regexextract(C3,"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}"),"")

When I use the function above, I do get the email addresses of rows 1 and 2 from the Data table, but not the email address from row 3.

I've started with

=query(data,"SELECT *",1)

to repeat the cells in the Data table and am trying to substitute code for the *, but am getting errors. For example, I tried

=query(data,"SELECT Regexextract(A,"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}")",1)

which shows an error. I prefer to use the query function so that I don't have to copy the Regexextract function down a column.

Answer 1

QUERY is not designed to have a formula autopopulate without dragging it down. You should use ARRAYFORMULA for this.

Assuming the formula you have is correct, the formula would be:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(A2:A4,"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}")))

Replace A2:A4 with your range.