TEXTBEFORE in Excel: what it does and how to use it

TEXTBEFORE returns everything that comes before a marker you name, such as a space, a dash or an at sign. Its twin, TEXTAFTER, returns everything on the other side.

Syntax

TEXTBEFORE(text, delimiter, [instance_num])

text: The value to cut up, usually a cell reference.
delimiter: The marker to stop at, in quotes. A space is " ", a dash is "-".
instance_num: Which occurrence of that marker to stop at. Leave it out and it stops at the first. Give it 2 and it stops at the second, so TEXTBEFORE("a-b-c","-",2) returns a-b. A negative number counts back from the end.

When to use it

Splitting one column into two. First name out of a full name, the part of a code before the dash, the piece of a reference before a slash.

What trips people up

It needs Microsoft 365. On older versions the same split is done with LEFT and FIND together: FIND locates the marker, LEFT takes everything up to it. That is why the older formula looks so much longer for the same job.

Watch it built, step by step

Split first and last name — First and last name
Get the text between two dashes — For codes like INV-1042-B
Split a code into two columns — Letters into one column, number into the next

Questions

What if the character is not in the text?

TEXTBEFORE returns #N/A. Give it a sixth argument to show instead, or wrap it in IFERROR.

How do I get the text before the last dash?

Use -1 as the third argument: =TEXTBEFORE(A2,"-",-1). Negative numbers count from the end.

What can I use in older versions of Excel?

=LEFT(A2,FIND("-",A2)-1) does the same job in any version.

Related functions

TEXTAFTER — TEXTAFTER returns everything that follows a marker you name.
RIGHT — RIGHT takes a set number of characters from the end of a piece of text.
SUBSTITUTE — SUBSTITUTE swaps one piece of text for another wherever it appears.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.