Extracting e-mail address from string

Question

Hi,

I have a column of data containing strings that could look like this: "asiofma jdskvs dskcv word1 ronny@gmail.com word2 cmnbaiuo kvlnvlksdm" or "jdskvbdsj apsfmkl word1 carl@hotmail.com word2 bafff b jkd paokf", where word1 and word2 will always be the same. Any idea how I can extract the e-mail addresses from these strings? I have tried some versions of "Findstring" and "Substring", but it seems that I cannot find the right approach.

Thanks a lot in advance!

NicoleJohnson · Accepted Answer

Try a RegEx tool or one of the RegEx formulas in the Formula tool!

RegEx tool: Choose the Parse method, then type the following expression: .*word1\s(.*)\sword2.*

This will look for word1, then a space, then a marked group containing whatever comes before a space + word 2. Your new parsed field will just show the email address.

Formula tool: Formula = RegEx_Replace([Field1],".*word1\s(.*)\sword2.*","$1")

Similar to the parsing method in the RegEx tool, but you indicate that you want to keep only the 1st marked group ($1) in your output.

Alternatively, you could do a formula method using a couple Replace formulas to replace the word1 & word2 with a delimiter (such as Replace([Field1],"word1 ","|") and Replace([Field1]," word2","|") , then you could use the Text To Columns tool to split up the field on the delimiter |. This would give you your email address in the second column, with words on either end of word1 & word2 in the other two columns.

Hope one of these methods helps you out! :)

Cheers,

NJ