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 "abc@gmail.com" 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) |
|---|---|
| Result | 4 |
Step 2: Pick up the letters before the special character "@"
| Formula | =LEFT(A3, FIND("@",A3)-1) |
|---|---|
| Result | abc |
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)