Pull Email Address from a cell

[LearnVBA83]

Hi all,

I have a column in my data called item description that looks like the below data set. What type of formula would you use to pull the email out of this data? I have 25,000 lines and for example below i just want a formula to pull John.Doe@Company.com. This will allow me to pull everyone's email from the data.

 

[Jeffrey Mahoney]

Please try this:
=LET(Ary,TEXTSPLIT(K3," ",,TRUE),M,MATCH("*@*",Ary,0),INDEX(Ary,,M))
Where K3 is the cell with the string containing the the email address

 

[LearnVBA83]

Please try this:
=LET(Ary,TEXTSPLIT(K3," ",,TRUE),M,MATCH("*@*",Ary,0),INDEX(Ary,,M))
Where K3 is the cell with the string containing the the email address

This seems to work well! Can you explain what the Ary is doing?

 

[Fluff]

Another option based on that single sample

=TEXTAFTER(A2,"email: ",,1)

 

[Jeffrey Mahoney]

The LET function allows you to create variables within the formula. Ary is a variable named by me. It stores the different values returned by TEXTSPLIT.

 

[Jeffrey Mahoney]

The next variable M stores the match found by looking for "@". Then finally Index returns the 5th value in the Array using INDEX