Practice free

TEXTBEFORE function

TEXTBEFORE returns everything that comes before a chosen delimiter, such as the username in an email address. It is easier than combining LEFT with FIND, and returns #N/A if the delimiter is missing.

Example

Read it as a sentence: return everything before the second hyphen in a-b-c, which gives a-b.

Practice this in the app

Syntax

TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Arguments in [square brackets] are optional.

text (required)
The text to search.
delimiter (required)
The marker to look for.
instance_num (optional)
Which occurrence; negative counts from the end. Default 1.
match_mode (optional)
0 case-sensitive (default), 1 ignore case.
match_end (optional)
1 treats the end of text as a delimiter.
if_not_found (optional)
Value to return instead of #N/A.

Returns: The text preceding the delimiter.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=TEXTBEFORE("a-b-c","-") a First
=TEXTBEFORE("a-b-c","-",2) a-b Second instance
=TEXTBEFORE("a-b-c","-",-1) a-b From end
=TEXTBEFORE("a-b-c","_",,,,"none") none if_not_found
=TEXTBEFORE("aXb","x",,1) a Case-insensitive
=TEXTBEFORE("a-b","-",-2,,1) match_end from start
=TEXTBEFORE("a-b-c","_") #N/A Not found
=TEXTBEFORE("a-b","-",0) #VALUE! Instance 0

Watch out for

  • Returns #N/A if the delimiter is missing.
  • Instance 2 returns the text before the second delimiter, including the first part.

Where it works

Excel
TEXTBEFORE arrived in Excel 2024. Older versions do not have it. Excel 2024 and Microsoft 365.
Google Sheets
No direct equivalent. Use REGEXEXTRACT or LEFT/FIND.

Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .