The formula
Excel 365 – before @Excel 365
=TEXTBEFORE(A2,"@")In C2 → maria.lopez
Excel 365 – after @Excel 365
=TEXTAFTER(A2,"@")In D2 → shop.co
Any Excel & Google Sheets – before @ExcelGoogle Sheets
=LEFT(A2,FIND("@",A2)-1)In C3 → maria.lopez
Any Excel & Google Sheets – after @ExcelGoogle Sheets
=MID(A2,FIND("@",A2)+1,LEN(A2))In D3 → shop.co
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 2 results for formulas tagged Excel 365 were checked with a reference implementation of the documented behavior, because LibreOffice can't run those functions.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | maria.lopez@shop.co | maria.lopez | shop.co | |
| 3 | maria.lopez | shop.co |
LibreOffice Calc can't run TEXTBEFORE/TEXTAFTER, so those were checked with a reference implementation; the LEFT/MID/FIND versions were verified in LibreOffice.
How it works
- FIND("@",A2)
- Position of the @ (12 here). FIND is case-sensitive; SEARCH is not.
- LEFT(A2,…-1)
- Everything up to, but not including, the @.
- MID(A2,…+1,LEN(A2))
- Everything from the character after the @ to the end.
- TEXTBEFORE(A2,"@",-1)
- Optional instance_num: -1 uses the last @ instead of the first.
Common errors and fixes
Why does the formula return #VALUE! (LEFT/FIND)?
The character isn't in the cell. Wrap in IFERROR(…,A2).
Why does the formula return #N/A (TEXTBEFORE)?
Delimiter not found. Use the if_not_found argument: TEXTBEFORE(A2,"@",,,,A2).
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.