You often need part of a text: the username from an email, the code before a dash, or the domain after the @ sign.
Modern way: TEXTBEFORE and TEXTAFTER
In Microsoft 365 and Excel 2024 you can write:
- Before the @:
=TEXTBEFORE(A2, "@") - After the @:
=TEXTAFTER(A2, "@")
For priya@example.com these return priya and example.com.
Classic way: LEFT, RIGHT, FIND, LEN
These work in every version of Excel.
- Text before the @:
=LEFT(A2, FIND("@", A2)-1) - Text after the @:
=RIGHT(A2, LEN(A2)-FIND("@", A2))
FIND gives the position of the character. LEFT takes everything up to just before it. RIGHT takes everything after it, using LEN to work out how many characters remain.
Example: SKU codes
For LAP-2045-BLK, =TEXTBEFORE(A2, "-") returns LAP. To get the second part, use =TEXTBEFORE(TEXTAFTER(A2, "-"), "-"), which returns 2045.
If the character is missing
FIND returns #VALUE! when the character is not in the cell. Wrap it with IFERROR to show the original text instead: =IFERROR(LEFT(A2, FIND("@", A2)-1), A2).
To split into several columns at once, see TEXTSPLIT or the Text to Columns tool.