Pulling out the part of a cell before or after a character is one of the most common text jobs in a spreadsheet: the name before the @ in an email address, the extension after the last dot in a file name, the code between two hyphens. If you come from Excel, you may reach for TEXTBEFORE and TEXTAFTER. In our test, Google Sheets didn’t recognise them. This page shows the formulas that worked instead.
Tested in Google Sheets. On November 11, 2026 we ran every formula on this page on the same sample values and copied the result or the exact error message. How we test
Found in testing: =TEXTBEFORE(A2, "@") returned #NAME? with the message Unknown function: ‘TEXTBEFORE’. TEXTAFTER and TEXTSPLIT gave the same result. These are Excel functions; on the date of our test they were not available in Google Sheets. If you paste a formula from an Excel tutorial and get #NAME?, this is a likely reason.
Quick reference
Examples use john.smith@example.com and 2026/11/11-report-final.pdf in A2.
| You want | Formula | Result |
|---|---|---|
| Text before the first @ | =LEFT(A2, FIND("@", A2)-1) |
john.smith |
| Text after the first @ | =MID(A2, FIND("@", A2)+1, LEN(A2)) |
example.com |
| Text after the last hyphen | =REGEXEXTRACT(A2, "[^-]+$") |
final.pdf |
| Text before the last hyphen | =REGEXEXTRACT(A2, "^(.*)-") |
2026/11/11-report |
| File extension (after the last dot) | =REGEXEXTRACT(A2, "[^.]+$") |
|
| Text between the first and second hyphen | =REGEXEXTRACT(A2, "^[^-]*-([^-]*)") |
report |
| The third piece | =INDEX(SPLIT(A2, "-"), 1, 3) |
final.pdf |
| Text before the first – or / | =REGEXEXTRACT(A2, "^[^-/]+") |
2026 |
Before a character: LEFT and FIND
FIND returns the position of the character, so FIND("@", A2) is 11 in our email address, and LEFT takes everything before it:
=LEFT(A2, FIND("@", A2)-1)
It returned john.smith. Two shorter alternatives gave the same result: =REGEXEXTRACT(A2, "^[^@]+"), which reads “everything from the start that isn’t an @”, and =REGEXREPLACE(A2, "@.*", ""), which deletes the @ and everything after it.
After a character: MID and FIND
=MID(A2, FIND("@", A2)+1, LEN(A2))
MID starts one character after the @ and takes up to LEN(A2) characters, which is always enough to reach the end. It returned example.com, as did =REGEXEXTRACT(A2, "@(.+)$") and =INDEX(SPLIT(A2, "@"), 2).
After the last occurrence
LEFT, MID and FIND always work from the first match. For the last one, a regular expression anchored to the end of the text is much simpler. [^-]+$ means “the characters that aren’t a hyphen, right up to the end”:
=REGEXEXTRACT(A2, "[^-]+$")
On 2026/11/11-report-final.pdf it returned final.pdf. Change the character to get the extension: "[^.]+$" returned pdf. For everything before the last hyphen, =REGEXEXTRACT(A2, "^(.*)-") returned 2026/11/11-report, because .* grabs as much as it can.
The SPLIT version also works when you prefer not to write a regular expression: =INDEX(SPLIT(A2, "-"), COLUMNS(SPLIT(A2, "-"))) returned final.pdf.
Between two characters, or the nth piece
For the text after the first hyphen and before the second, the regular expression captures what is in brackets:
=REGEXEXTRACT(A2, "^[^-]*-([^-]*)")
It returned report. The FIND version needs three FINDs and is easy to get wrong, but it also works: =MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1)-FIND("-", A2)-1). For the nth piece, SPLIT and INDEX are the clearest: =INDEX(SPLIT(A2, "-"), 1, 3) gave the third piece, final.pdf. With a regular expression, =REGEXEXTRACT(A2, "^(?:[^-]*-){2}([^-]*)") skips two pieces and returned the same.
When the character isn’t there
FIND fails when it can’t find the character. With # instead of @, =LEFT(A2, FIND("#", A2)-1) returned #VALUE! and the message In FIND evaluation, cannot find ‘#’ within ‘john.smith@example.com’. REGEXEXTRACT returns #N/A instead, with does not match text. Decide what you want in that case:
- The whole cell:
=IFERROR(LEFT(A2, FIND("#", A2)-1), A2)returned the full address. - A label:
=IFNA(REGEXEXTRACT(A2, "#(.*)"), "none")returned “none”.
Upper and lower case
FIND is case-sensitive; SEARCH is not. To get the name after “Name: ” whatever its case, =MID(A2, SEARCH("name: ", A2)+LEN("name: "), 99) returned Ana from “Name: Ana”. In a regular expression, start with (?i): =REGEXEXTRACT(A2, "(?i)JOHN") found “john”.
The result is text, even when it looks like a number
LEFT, MID and REGEXEXTRACT always return text. =REGEXEXTRACT("Order 123 shipped", "\d+") returned “123”, and ISTEXT confirmed it was text, so SUM will skip it. Wrap the formula in VALUE when you need a number. SPLIT is different: it turns pieces that look like numbers into real numbers. More on both in converting text to numbers and splitting text.