Excel track · Exercise 54 of 86 Paid

TEXTBEFORE and TEXTAFTER: split on a delimiter

Split a code at its delimiter, taking either the part before or the part after.

Advanced

Order codes: a three-letter prefix, a hyphen, a number

Read-only data · screen 1 of 2 · answer in B2

In B2, read the three letters before the hyphen in the order code in A2.

	A	B
1	Order	Prefix
2	ORD-4471
3	INV-2210
4	RCP-9008

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In B2, read the four digits after the hyphen in the order code in A2.

	A	B
1	Order	Number
2	ORD-4471
3	INV-2210
4	RCP-9008

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

TEXTBEFORE and TEXTAFTER split a cell on a delimiter, one function per side. You name the delimiter as a piece of text, so no position number appears anywhere in the formula. Each returns the half on its side and drops the delimiter itself.

The formula =TEXTBEFORE(A2,"-") returns ORD from the code ORD-4471, and TEXTAFTER with the same arguments returns 4471. Move both formulas to a column of five-letter prefixes and they keep working, because the delimiter still marks the join.

When the delimiter is missing, both functions return #N/A instead of inventing a half. A code that does not fit the pattern is exactly the row you want to see fail, since a blank answer would hide it. Read the error as a prompt to clean that row, not as a fault in the formula.

Syntax

=TEXTBEFORE(text,"-") keeps what precedes the delimiter; =TEXTAFTER keeps what follows

Worked example

On Order codes: a three-letter prefix, a hyphen, a number, =TEXTBEFORE(A2,"-") returns ORD.

=TEXTBEFORE(A2,"-")

Common errors

Error codes for TEXTBEFORE and TEXTAFTER naming the delimiter instead of a position
Error	What it means and how to fix it
`#N/A`	The cell holds no copy of the delimiter, which leaves no place to split.
`#NAME?`	The workbook was built in an Excel version older than the one that added these functions.
`Swapped halves`	The two functions differ by one word, and the wrong one returns the other half of the code.

At work

Separating an order prefix from its number without counting characters.