Available in:Excel 2024 or later (Works in Excel 2024, every later version, Microsoft 365 and Excel for the web)Category:Text

TEXTBEFORE

TEKST.VOOR in Dutch Excel

Returns the part of a text that sits before a chosen delimiter, counting from the start or from the end, without LEFT and FIND.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C2 holds the formula
Row	A	C
1	Relation (export)
2	D1001 - Kaya Design BV	D1001=TEXTBEFORE(A2, " - ")=TEXTBEFORE(A2; " - ")
3	D1002 - Molenaar Bouw BV
4	C2001 - Bakker-Smit Installatie BV
5	C2002 - Hendriks Metaal BV

Result: D1001

=TEXTBEFORE(A2, " - ")

=TEXTBEFORE(A2; " - ")

What TEXTBEFORE does and when to use it

What it does: it returns the part of a text in front of a delimiter, for example the relation code in front of the separator in an exported name.

When to use it: when a code, a folder or a name sits in front of a fixed separator, such as the folder in a file path or the invoice number in a bank description. It replaces LEFT with FIND and a minus one. A negative instance_num suits texts of varying length whose last part is fixed. For every part at once, TEXTSPLIT is better.

Two main gains:

No position arithmetic: you name the delimiter, so there is no FIND and no minus one.
Your own result when nothing is found: if_not_found shows your message instead of #N/A, and match_end treats the end of the text as a delimiter.

How Excel evaluates it

Excel first looks for the delimiter in text, using the case rule from match_mode. With a positive instance_num it counts delimiters from the left; with a negative one it counts from the right. It cuts at the chosen delimiter and returns everything to its left, without the delimiter itself.

If the text has fewer delimiters than asked for, the result is if_not_found, or #N/A when you left it out. With match_end set to 1, the end of the text counts as an extra delimiter first, so the whole text comes back instead of an error. If text is a range, this is done for every cell and the results spill.

Watch out: TEXTBEFORE is case-sensitive by default: set match_mode to 1 when capitals vary, or cells with Factuur or FACTUUR are skipped when you search for factuur.

Mental model

A pair of scissors that you place at the n-th separator, counted from either end, keeping the left-hand piece.

Terms used on this page: error value, spill, argument, range, cell

Syntax

Official TEXTBEFORE documentation

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

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

Arguments
Argument	Type	Meaning	Default
`text`	Required	The text to cut, as a cell, a text or a range of cells.
`delimiter`	Required	The character or text that marks where to cut.
`instance_num`	Optional	Which occurrence of the delimiter to cut at; negative counts from the end.	`1`
`match_mode`	Optional	0 is case-sensitive (default), 1 ignores case.	`0`
`match_end`	Optional	1 treats the end of the text as a delimiter.	`0`
`if_not_found`	Optional	What to return when no delimiter is found; default #N/A.	`#N/A`

Return value:The text before the chosen occurrence of the delimiter

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Split the relation code off an exported name

Sanne de Vries is checking the relation list of Veldhuis Installatie BV. The export puts the relation code and the name in one cell, separated by a space, a hyphen and a space. She needs the code alone, so she can match it to the receivables and payables lists.

Sheet excerpt: C2 holds the formula
Row	A	C
1	Relation (export)
2	D1001 - Kaya Design BV	D1001=TEXTBEFORE(A2, " - ")=TEXTBEFORE(A2; " - ")
3	D1002 - Molenaar Bouw BV
4	C2001 - Bakker-Smit Installatie BV
5	C2002 - Hendriks Metaal BV
6

Formula cell (C2)

Formula in C2

=TEXTBEFORE(A2, " - ")

=TEXTBEFORE(A2; " - ")

Result: D1001

Step by step

Excel reads text, the cell A2, and finds D1001 - Kaya Design BV. The delimiter is the three characters space, hyphen, space.
instance_num is not given, so it is 1: the first occurrence. D1001 has 5 characters, so the first space-hyphen-space starts at position 6.
Excel keeps everything before position 6, the 5 characters D1001, and leaves out the delimiter and the name. C2 shows D1001.
Copy the formula down and C3 shows D1002, C4 shows C2001 and C5 shows C2002. In A4 the name contains a hyphen (Bakker-Smit), but that hyphen has no spaces around it, so it is not the delimiter.

Watch out: A cell without the space-hyphen-space, for example a relation typed as D1005 Smits, shows #N/A. Look for the cells that show an error: they are the rows where the export differs from the others.

More on this example: why it works, 1 variation

Why it works

The delimiter is more than one character, which is why the hyphens inside names are left alone. TEXTBEFORE works from the delimiter you name, so there is no position to count and no minus one to subtract.

Variations

=LEFT(A2, FIND(" - ", A2) - 1)

=LEFT(A2; FIND(" - "; A2) - 1)

The older construction that works in every version: find the position, subtract 1, take that many characters.

Intermediate

Find the folder of each audit file, whatever its depth

In the shared audit file of Veldhuis Installatie BV you have a list of file paths. Some files sit three levels deep, others five. You want the folder of each file, which is the path without the file name, to check that confirmations and bank statements are filed in the right place.

Because the depth varies, only the last slash is a reliable cut.

Sheet excerpt: C2 holds the formula
Row	A	C
1	File path
2	Veldhuis/2025/Debiteuren/Confirmaties/conf-Smits.pdf	Veldhuis/2025/Debiteuren/Confirmaties=TEXTBEFORE(A2, "/", -1)=TEXTBEFORE(A2; "/"; -1)
3	Veldhuis/2025/Bank/afschrift-dec.pdf
4	Veldhuis/2025/Loon/loonjournaal.xlsx
5	Veldhuis/2025/Voorraad/Telling/Lijst/voorraad.xlsx
6

Formula cell (C2)

Formula in C2

=TEXTBEFORE(A2, "/", -1)

=TEXTBEFORE(A2; "/"; -1)

Result: Veldhuis/2025/Debiteuren/Confirmaties

Step by step

Excel reads A2: Veldhuis/2025/Debiteuren/Confirmaties/conf-Smits.pdf. The delimiter is a slash.
The path holds 4 slashes: after Veldhuis, after 2025, after Debiteuren and after Confirmaties.
instance_num is -1, so Excel counts from the right and picks the last slash, the 4th one, directly before conf-Smits.pdf. It returns everything to its left: Veldhuis/2025/Debiteuren/Confirmaties.
Copied down, C3 gives Veldhuis/2025/Bank (3 slashes, last one before afschrift-dec.pdf), C4 gives Veldhuis/2025/Loon and C5 gives Veldhuis/2025/Voorraad/Telling/Lijst (5 slashes). The same formula handles every depth.
Without the -1, instance_num would be 1 and A2 would give only Veldhuis. With 3, A2 would give Veldhuis/2025/Debiteuren, which is not the folder of the file.

Watch out: A path that uses backslashes, as Windows shows it, contains no slash at all and shows #N/A. Check which slash the export uses before you copy the formula.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Counting from the right turns the question around: instead of asking how deep the file sits, you ask for the last slash, which is the one in front of the file name in every path. The file name is the part that differs, so the cut belongs there.

More to watch out for

A path that ends in a slash, such as a folder name without a file, comes back as the path without its trailing slash, so it still includes its last folder and not the parent folder. Check that every row ends in a file name.

Variations

=TEXTAFTER(A2, "/", -1)

=TEXTAFTER(A2; "/"; -1)

The mirror image: the file name alone, everything after the last slash.

Advanced

Pull the relation code from the middle of a posting description

The description field of the XAF auditfile of Veldhuis Installatie BV holds four parts separated by a pipe (|):

journal
entry number
relation code
relation name

Bank lines have no name, so their description stops after the code. You want the relation code of every line in one formula, to match them to the debtor and creditor lists.

Sheet excerpt: C2 holds the formula
Row	A	C
1	Description
2	INK\|25-0412\|C2001\|Bakker-Smit Installatie BV	C2001=TEXTBEFORE(TEXTAFTER(A2:A6, "\|", 2), "\|", 1, 0, 1)=TEXTBEFORE(TEXTAFTER(A2:A6; "\|"; 2); "\|"; 1; 0; 1)
3	VRK\|25-0413\|D1001\|Kaya Design BV	D1001
4	BNK\|25-0414\|D1002	D1002
5	MEM\|25-0415\|C2002\|Hendriks Metaal BV	C2002
6	BNK\|25-0416\|D1003	D1003
7

Formula cell (C2)Spilled result

Formula in C2

=TEXTBEFORE(TEXTAFTER(A2:A6, "|", 2), "|", 1, 0, 1)

=TEXTBEFORE(TEXTAFTER(A2:A6; "|"; 2); "|"; 1; 0; 1)

Result: C2001 / D1001 / D1002 / C2002 / D1003C2001 / D1001 / D1002 / C2002 / D1003

Step by step

The inner function runs first: TEXTAFTER(A2:A6, "|", 2) takes everything after the 2nd pipe in each of the 5 cells.
That gives five texts: C2001|Bakker-Smit Installatie BV, D1001|Kaya Design BV, D1002, C2002|Hendriks Metaal BV and D1003. The two bank lines have only 2 pipes, so after the 2nd one only the code is left.
The outer TEXTBEFORE cuts each of these at the 1st pipe (instance_num 1, match_mode 0). For the first text the pipe sits after C2001, so C2001 comes back; the others give D1001 and C2002 in the same way.
For D1002 and D1003 there is no pipe left. match_end is 1, so the end of the text counts as the delimiter and the whole text comes back: D1002 and D1003.
The five results spill from C2 to C6: C2001, D1001, D1002, C2002, D1003. Without the last two arguments, C4 and C6 would show #N/A.

Watch out: The formula takes the 3rd field by position. If one line has an extra field in front, for example a second entry number, it returns a name or a number and no error appears. Sort the results and look for values that are not a C or D followed by four digits.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

TEXTAFTER removes the front of the text and TEXTBEFORE removes the back, so together they take out a middle part. The two functions are mirror images, which is why the same delimiter appears in both. match_end is what lets the last field of a line be a middle part too.

More to watch out for

A line with fewer than 2 pipes, such as MEM|26-0003, makes the inner TEXTAFTER show #N/A, and the outer function passes that error on.

Variations

=INDEX(TEXTSPLIT(A2, "|"), 3)

=INDEX(TEXTSPLIT(A2; "|"); 3)

Splits one line into its parts and takes the 3rd. Longer, but a better choice when you need two or three of the parts. It works on one cell at a time.

Master

Find the invoice number mentioned in bank descriptions

You are matching the bank statement of Veldhuis Installatie BV to the receivables ledger. The payer typed a description themselves, so:

the word factuur appears in any capitalization
the number may be followed by a name or may end the text
some lines mention no invoice

You want the invoice number for each line, or a clear note, in one readable formula that the next reviewer can follow.

Sheet excerpt: C2 holds the formula
Row	A	C
1	Bank description
2	Betaling Factuur 25-1101 Kaya Design BV	25-1101=LET( description, A2:A7, after_label, TEXTAFTER(description, "factuur ", 1, 1, 0, ""), IF(after_label = "", "No invoice mentioned", TEXTBEFORE(after_label, " ", 1, 0, 1)) )=LET( description; A2:A7; after_label; TEXTAFTER(description; "factuur "; 1; 1; 0; ""); IF(after_label = ""; "No invoice mentioned"; TEXTBEFORE(after_label; " "; 1; 0; 1)) )
3	Ontvangst factuur 25-1104	25-1104
4	Salaris december	No invoice mentioned
5	Creditnota FACTUUR 25-1077 retour	25-1077
6	Incasso factuur 25-1108 Mulder Horeca BV	25-1108
7	Factuurnummer onbekend, navragen	No invoice mentioned
8

Formula cell (C2)Spilled result

Formula in C2

=LET(
  description, A2:A7,
  after_label, TEXTAFTER(description, "factuur ", 1, 1, 0, ""),
  IF(after_label = "", "No invoice mentioned", TEXTBEFORE(after_label, " ", 1, 0, 1))
)

=LET(
  description; A2:A7;
  after_label; TEXTAFTER(description; "factuur "; 1; 1; 0; "");
  IF(after_label = ""; "No invoice mentioned"; TEXTBEFORE(after_label; " "; 1; 0; 1))
)

Result: 25-1101 / 25-1104 / No invoice mentioned / 25-1077 / 25-1108 / No invoice mentioned25-1101 / 25-1104 / No invoice mentioned / 25-1077 / 25-1108 / No invoice mentioned

Step by step

LET stores description as A2:A7, the six bank lines. Nothing is calculated yet.
after_label cuts every line after the first "factuur " (the word and a space), ignoring case because match_mode is 1. Results: 25-1101 Kaya Design BV; 25-1104; empty; 25-1077 retour; 25-1108 Mulder Horeca BV; empty.
Line 3 has no label. Line 6 contains Factuurnummer, but after factuur comes an n, not a space, so it does not match either. Both give the empty text from if_not_found.
IF tests each after_label first. Where it is empty, lines 3 and 6, the result is No invoice mentioned, and for those lines IF returns the label instead of the TEXTBEFORE result.
Elsewhere TEXTBEFORE(after_label, " ", 1, 0, 1) keeps the part before the first space, and match_end 1 lets a text without a space count as complete: 25-1101; 25-1104 (no space at all, the end acts as delimiter); 25-1077; 25-1108.
The six results spill from C2 to C7: 25-1101, 25-1104, No invoice mentioned, 25-1077, 25-1108, No invoice mentioned. To debug, replace the last line of the LET with after_label to see the first intermediate result.

Watch out: The mirror-image mistake is leaving match_mode at 0. Lines 1 and 4 contain Factuur and FACTUUR, so they no longer match and show No invoice mentioned. The result looks like a list of descriptions without a reference, so nobody suspects the formula.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Each name is a step you can read and test on its own, and both functions run on all six lines at once.

Do not use this construction when the file must open in Excel 2021 or earlier, because TEXTAFTER and TEXTBEFORE do not exist there, or for a one-off clean-up: Flash Fill or Power Query (Extract, Text After Delimiter) does the same without a formula, at the cost of running it again when the data changes.

More to watch out for

Leaving out match_end in TEXTBEFORE has a different effect on line 2, where the number ends the text: the cut finds no space, so TEXTBEFORE returns #N/A for that line instead of 25-1104, and IF passes that error on rather than showing a label.
Descriptions such as factuur 25-1101 en 25-1102 yield only the first number. The formula returns one reference per line.

Variations

=TEXTBEFORE(TEXTAFTER(A2, "factuur ", 1, 1), " ", 1, 0, 1)

=TEXTBEFORE(TEXTAFTER(A2; "factuur "; 1; 1); " "; 1; 0; 1)

A shorter form for one line, without a message. If the label is missing, TEXTAFTER shows #N/A and so does the result, which is clearer than a blank when you check a single line.

Common mistakes

Common mistakes
Symptom	Cause	Fix
#N/A in cells without the delimiter	Some cells show #N/A, usually the ones where the text is shorter or does not contain the delimiter at all.	Decide what such a cell should give. match_end 1 returns the whole text when the delimiter is the last thing missing; if_not_found returns a message you choose. Do not wrap the whole formula in IFERROR, because that also hides real mistakes.
The folder is too short, no error	You cut at the third slash to get the folder, and files in deeper folders return a folder that is too short, without any error.	When the part you want to drop is always the last one, count from the end with instance_num -1. A positive number only works when every text has the same structure.
Cells with other capitals are skipped	The delimiter is a word, such as `factuur`, and cells with `Factuur` or `FACTUUR` are skipped.	Capitals almost always vary in exported descriptions, so set match_mode to 1.
#VALUE! or #N/A with the delimiter present	The result is #VALUE! or #N/A, although the delimiter is in the text.	instance_num is 0, which gives #VALUE!, or larger than the number of delimiters in text, which gives #N/A (or if_not_found, when you supplied it). Use 1 or -1 for the first and the last delimiter.

Used in combinations

Find the customer from a bank descriptionCut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.Excel 2024 or later (This combination needs Excel 2024 or later)