How to Extract Text before at sign for Email Address

To extract the text before the at sign (@), you need to find the location of the special character of the at sign (@) and then use the Left Function. Please see below for details

Formula:

=LEFT(A2,FIND("@",A2)-1)

Where cell A1 is the first cell with the email address.

Copy the formula and replace "A1" with your own data for the results.

Example:

To extract text before the special character "@" in the email address "[email protected]" in cell A3, as shown above.

The result returns the text "abc".

Explanations:

Step 1: To find the location of the special character "@"

Formula=FIND("@",A3)
Result4

Step 2: Pick up the letters before the special character "@"

Formula=LEFT(A3, FIND("@",A3)-1)
Resultabc

If only part of your data contain the at sign (@), you may use the IFERROR function to have all the text for those do not have the special characters.

=IFERROR(LEFT(A2,FIND("@",A2)-1),A2)

Or make the cells without the at sign blank with the following formula.

=IFERROR(LEFT(A2,FIND("@",A2)-1),"")

In case your data contains a special character that is different from the at sign (@), please replace the at sign (@) with the special character in your data, e.g., "]".

=LEFT(A1, FIND("]",A1)-1)

Leave a Reply