extracting email addresses

[Rhon Alvarez]

Hi Team,

i got this text file from our IT Group wherein i need to extract the email address per row. i tried this formula but i can't seems to figure out how to obtain the correct answer.

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("tdcx.com",A1),SEARCH(" ",A1,SEARCH("tdcx.com",A1))-SEARCH("tdcx.com",A1)),"-",REPT(" ",255)),255)),"")

kindly assist please.

Thanks in advance
Rhon

 

[bosco_yip]

Try,

In B1, formula copied down:

=IFERROR(MID(A1,FIND("""",A1)+1,FIND("^",SUBSTITUTE(A1,"""","^",2))-FIND("""",A1)-1),"")

 

[AlanSidman]

With Power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, """", """"), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Text Between Delimiters", each ([Column1] <> ""))
in
    #"Filtered Rows"