Formula to extract an email from a text string cell

Julie Hinton

Hello Smartsheet Community Friends!

I am looking for a formula that would allow me to extract just an email address from a cell that contains text. Example, the cell in column "Notes" might contain:

'blah blah words blah myemail@email.com more words blah blah'

I am looking for a formula that would help me extract that email address and copy into a new "Email" column

I am thinking this would be a lookup for @ and then taking all text left or right of that up to the space, but I don't know how I could write that. Does anyone have experience with that?

Thanks!! ✌️

Lidiya Shutaya

Hello @Julie Hinton

Hope you are doing well! Gathering the email domain (@abc.com) is relatively straight forward, but getting that initial address is convoluted.

To start, I recommend at least 3 helper columns and then the full email can be displayed in a fourth one.

The first helper would call up everything left of the @:=LEFT([Column2]@row, FIND("@", [Column2]@row, 1) - 1)

The second would parse out just the address: =RIGHT([Column3]1, LEN([Column3]1) - FIND("!", SUBSTITUTE([Column3]1, " ", "!", LEN([Column3]1) - LEN(SUBSTITUTE([Column3]1, " ", ""))), 1))

Third would grab the email domain: =LEFT(MID([Column2]1, FIND("@", [Column2]1, 1), 256), FIND(" ", MID([Column2]1, FIND("@", [Column2]1, 1), 256), 1))

And the final column would put it all together: =[Column4]1 + [Column5]1

You definitely could combine it all into one formula but it might become hard to troubleshoot if something goes wrong. For example, this formula is not going to work if the user just enters their email (as shown in row 6).

Appendix type stuff -

Put the all in one formula in just in case you'd like to try it. Just replace [Column2]@row with your column name.

=RIGHT(LEFT([Column2]@row, FIND("@", [Column2]@row, 1) - 1), LEN(LEFT([Column2]@row, FIND("@", [Column2]@row, 1) - 1)) - FIND("!", SUBSTITUTE(LEFT([Column2]@row, FIND("@", [Column2]@row, 1) - 1), " ", "!", LEN(LEFT([Column2]@row, FIND("@", [Column2]@row, 1) - 1)) - LEN(SUBSTITUTE(LEFT([Column2]@row, FIND("@", [Column2]@row, 1) - 1), " ", ""))), 1)) + LEFT(MID([Column2]8, FIND("@", [Column2]@row, 1), 256), FIND(" ", MID([Column2]@row, FIND("@", [Column2]@row, 1), 256), 1))

What is going in the left hand address formula.

RIGHT formula wants to read from the right hand, but we want it to only read up to and not including the space. Unfortunately, the FIND formula always reads left to right and we only care about the last space if we are reading left to right. So the last SUBSTITUTE is where you tell SmartSheet that you want the space to be replaced with an !, then you are using LEN to count how many spaces are in the string. Using FIND you are saying "Tell me how many characters into the string the LAST space is" which is 21. Now you take the total length of the string, subtract 21 and you are left with 7 characters to the right of the space. Finally you use the RIGHT formula to say "Look at this string, give me the last 7". Side note: you can sub the space with any character that will likely not show up anywhere else. In excel, it is common to use SEARCH instead but that is not available in SmartSheet at this time.

I hope this helps! I put the explanations in just in case there are questions and also because I needed to logic through it in my own head LOL! If someone finds an easier/faster way of doing this, I would love to know!

Best,

Lidiya Shutaya