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

TEXTAFTER

TEKST.NA in Dutch Excel

Cuts a text at a delimiter and returns everything that comes after it, optionally after the n-th delimiter or counted from the end.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C2 holds the formula
Row	A	C
1	Account
2	1300 Debiteuren	Debiteuren=TEXTAFTER(A2, " ")=TEXTAFTER(A2; " ")
3	1600 Crediteuren
4	1800 Te betalen btw
5	4500 Huisvestingskosten

Result: Debiteuren

=TEXTAFTER(A2, " ")

=TEXTAFTER(A2; " ")

What TEXTAFTER does and when to use it

What it does: it returns the part of a text that follows a delimiter, for example the account name that follows the account number in a trial balance label.

When to use it: when one cell holds several facts in a fixed pattern, such as an account number with its name, a file path or a bank description with a reference. It replaces combinations of MID, FIND and LEN: you name the delimiter and Excel does the counting. Combined with TEXTBEFORE it takes a middle part.

Two main gains:

No counting: you name the delimiter, so there is no FIND, LEN or minus one to work out.
Occurrence and fallback: a negative instance_num counts from the end, and if_not_found shows your message instead of #N/A.

How Excel evaluates it

Excel works through the arguments in this order:

it looks at instance_num first, 1 when you leave it out
it scans text from the left for the delimiter, or from the right when the number is negative, until it has found the requested occurrence
everything behind that occurrence is the result, even when the text contains more delimiters further on
if the delimiter is not found, it returns if_not_found, or #N/A when you left it out; with match_end set to 1 the end (or, counting from the right, the start) of the text acts as the missing delimiter

With a range as text, the same happens for every cell and the answers spill.

Watch out: With match_end 1, TEXTAFTER returns empty text when the delimiter is missing, because the end of the text then acts as the delimiter. Use if_not_found to show a message instead (only TEXTBEFORE returns the whole text in that case).

Mental model

Scissors with a counter: you say which cut to make, the scissors cut there, and you keep the piece on the right.

Terms used on this page: reference, spill, argument, range, cell

Syntax

Official TEXTAFTER documentation

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

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

Arguments
Argument	Type	Meaning	Default
`text`	Required	The text to search in.
`delimiter`	Required	The character or text that marks where to cut.
`instance_num`	Optional	Which occurrence of the delimiter; 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.

Return value:The text after the chosen delimiter, or if_not_found (default #N/A) when the delimiter does not occur

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

Read the account name from a trial balance label

The client sends a trial balance of Veldhuis Installatie BV in which the account number and the account name share one cell, such as 1300 Debiteuren. Your own account mapping needs the name in a separate cell. You take the part after the first space.

Sheet excerpt: C2 holds the formula
Row	A	C
1	Account
2	1300 Debiteuren	Debiteuren=TEXTAFTER(A2, " ")=TEXTAFTER(A2; " ")
3	1600 Crediteuren
4	1800 Te betalen btw
5	4500 Huisvestingskosten
6

Formula cell (C2)

Formula in C2

=TEXTAFTER(A2, " ")

=TEXTAFTER(A2; " ")

Result: Debiteuren

Step by step

Excel reads A2: the text 1300 Debiteuren, 15 characters. The delimiter is one space, and instance_num is left out, so it is 1.
Scanning from the left, Excel passes 1, 3, 0, 0 (characters 1 to 4) and finds the first space at position 5.
Everything behind position 5 is the result: characters 6 to 15, which is Debiteuren (10 characters).
Copy the formula down. In C4 the label 1800 Te betalen btw gives Te betalen btw: only the first space counts, so the two later spaces stay inside the result.

Watch out: A label without a space, such as 1100Bank, returns #N/A. A label with a leading space makes the cut at position 1 and returns the whole label.

More on this example: why it works, 2 variations

Why it works

The function cuts at one place only: the occurrence you ask for, the first by default. Everything to the right of that cut is kept, whatever it contains, so names with spaces survive intact.

Variations

=TEXTBEFORE(A2, " ")

=TEXTBEFORE(A2; " ")

The mirror image: the account number, 1300, instead of the name.

=MID(A2, FIND(" ", A2) + 1, 100)

=MID(A2; FIND(" "; A2) + 1; 100)

The construction for Excel 2021 and earlier, where TEXTAFTER does not exist: FIND locates the space, MID takes the rest.

Intermediate

File name after the last slash, with a flag for loose files

Sanne de Vries is following up on balance confirmations for Veldhuis Installatie BV. The list of scanned replies holds full paths of different depth. She needs the file name, which is whatever follows the last slash. One reply was saved without any folder, and she wants it flagged instead of an error.

Sheet excerpt: C2 holds the formula
Row	A	C
1	Saved path
2	Debiteuren/2025/Kaya Design BV.pdf	Kaya Design BV.pdf=TEXTAFTER(A2:A6, "/", -1, 0, 0, "Not in a folder")=TEXTAFTER(A2:A6; "/"; -1; 0; 0; "Not in a folder")
3	Debiteuren/2025/Molenaar Bouw BV.pdf	Molenaar Bouw BV.pdf
4	Mulder Horeca BV.pdf	Not in a folder
5	Crediteuren/Smits Bouw BV.pdf	Smits Bouw BV.pdf
6	Debiteuren/2025/Van Dijk Transport BV.pdf	Van Dijk Transport BV.pdf
7

Formula cell (C2)Spilled result

Formula in C2

=TEXTAFTER(A2:A6, "/", -1, 0, 0, "Not in a folder")

=TEXTAFTER(A2:A6; "/"; -1; 0; 0; "Not in a folder")

Result: Kaya Design BV.pdf / Molenaar Bouw BV.pdf / Not in a folder / Smits Bouw BV.pdf / Van Dijk Transport BV.pdfKaya Design BV.pdf / Molenaar Bouw BV.pdf / Not in a folder / Smits Bouw BV.pdf / Van Dijk Transport BV.pdf

Step by step

The text argument is the whole range A2:A6, so Excel repeats the cut for each of the 5 paths. instance_num is -1: count slashes from the right and take the first one found.
A2 is Debiteuren/2025/Kaya Design BV.pdf. Slashes sit at position 11 (after Debiteuren, 10 characters) and 16 (after 2025). From the right the first slash is the one at 16, so the result is Kaya Design BV.pdf.
A3 gives Molenaar Bouw BV.pdf in the same way. A5, Crediteuren/Smits Bouw BV.pdf, has one slash at position 12 and gives Smits Bouw BV.pdf. A6 gives Van Dijk Transport BV.pdf.
A4, Mulder Horeca BV.pdf, has no slash at all. Without the sixth argument this row would be #N/A; with if_not_found it shows Not in a folder. The arguments 0 and 0 in between are the defaults for match_mode and match_end, filled in only to reach the sixth place.
Five answers come back, so the result spills from C2 to C6.

Watch out: Paths exported from Windows use a backslash. If the delimiter is a slash and the paths hold backslashes, every row shows Not in a folder, which looks like a data problem rather than a wrong delimiter.

More on this example: why it works, 1 variation

Why it works

A negative instance_num makes the number of folder levels irrelevant: it does not matter whether a path has one slash or five, the last one is always the cut. if_not_found turns a missing delimiter from an error that stops a column into a label you can filter on.

Variations

=TEXTAFTER(A2:A6, "/", -1, 0, 1)

=TEXTAFTER(A2:A6; "/"; -1; 0; 1)

With match_end 1 and a negative instance_num, the start of the text acts as the delimiter, so a loose file returns its own name instead of a flag.

Advanced

Customer name from the middle of a journal description

In the XAF auditfile of Veldhuis Installatie BV the description of each sales line reads invoice number, customer name and, in most cases, relation code, separated by a vertical bar with spaces around it. To match the lines with the aging list you need the customer name alone. One line has no relation code yet.

Sheet excerpt: C2 holds the formula
Row	A	C
1	Description
2	25-1101 \| Kaya Design BV \| D1001	Kaya Design BV=TEXTBEFORE(TEXTAFTER(A2:A6, " \| "), " \| ", 1, 0, 1)=TEXTBEFORE(TEXTAFTER(A2:A6; " \| "); " \| "; 1; 0; 1)
3	25-1102 \| Molenaar Bouw BV \| D1002	Molenaar Bouw BV
4	25-1103 \| Van Dijk Transport BV	Van Dijk Transport BV
5	25-1104 \| Hendriks Metaal BV \| D1004	Hendriks Metaal BV
6	25-1105 \| Mulder Horeca BV \| D1005	Mulder Horeca BV
7

Formula cell (C2)Spilled result

Formula in C2

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

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

Result: Kaya Design BV / Molenaar Bouw BV / Van Dijk Transport BV / Hendriks Metaal BV / Mulder Horeca BVKaya Design BV / Molenaar Bouw BV / Van Dijk Transport BV / Hendriks Metaal BV / Mulder Horeca BV

Step by step

Excel evaluates the inner function first: TEXTAFTER(A2:A6, " | ") cuts each description at its first bar and keeps what follows.
That gives, row by row:
- Kaya Design BV | D1001
- Molenaar Bouw BV | D1002
- Van Dijk Transport BV
- Hendriks Metaal BV | D1004
- Mulder Horeca BV | D1005
The invoice numbers are gone.
The outer TEXTBEFORE cuts each of these at its first bar and keeps what comes before: Kaya Design BV, Molenaar Bouw BV, Hendriks Metaal BV and Mulder Horeca BV. The relation codes are gone.
The third row, Van Dijk Transport BV, contains no second bar. The arguments 1, 0, 1 are instance_num 1, match_mode 0 and match_end 1: the end of the text counts as the delimiter, so the whole name comes back. Without match_end this row would be #N/A.
Five names come back, so the result spills from C2 to C6.

Watch out: If the delimiter is typed as "|" without the spaces, every name comes back with a space at the front and at the back. The list looks correct, but an XLOOKUP against the aging list finds nothing for any customer.

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

Why it works

Each function removes one side of the text: TEXTAFTER drops the left part, TEXTBEFORE drops the right part, and what remains is the middle. Because the inner result is itself an array, the outer function works on all rows at once.

More to watch out for

Wrapping the formula in IFERROR to get rid of the #N/A hides lines whose description has a different layout altogether. Use match_end for the known gap and let unexpected errors show.

Variations

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

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

Stop before the second bar: this returns the invoice number together with the name, for example 25-1101 | Kaya Design BV.

Master

Invoice numbers from free-text bank descriptions

To match bank receipts of Veldhuis Installatie BV with the receivables ledger, you need the invoice number out of the free text on the bank statement. The number follows the word factuur, written as factuur, Factuur or FACTUUR, and is followed by a name or nothing at all. Lines without the word, such as salaries, must be labeled instead of showing an error. The formula must be readable for the reviewer.

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	Bank description	Amount
2	Betaling factuur 25-1101 Kaya Design BV	€6,050.00€ 6.050,00	25-1101=LET( lines, A2:A7, after, TEXTAFTER(lines, "factuur ", 1, 1, 0, ""), IF(after = "", "No invoice", TEXTBEFORE(after, " ", 1, 0, 1)) )=LET( lines; A2:A7; after; TEXTAFTER(lines; "factuur "; 1; 1; 0; ""); IF(after = ""; "No invoice"; TEXTBEFORE(after; " "; 1; 0; 1)) )
3	FACTUUR 25-1104 Hendriks Metaal	€3,025.00€ 3.025,00	25-1104
4	Salaris december	€41,250.00€ 41.250,00	No invoice
5	Creditnota na factuur 25-1103	€1,210.00€ 1.210,00	25-1103
6	Overboeking spaarrekening	€25,000.00€ 25.000,00	No invoice
7	Ontvangen factuur 25-1106 Smits Bouw BV	€9,680.00€ 9.680,00	25-1106
8

Formula cell (D2)Spilled result

Formula in D2

=LET(
  lines, A2:A7,
  after, TEXTAFTER(lines, "factuur ", 1, 1, 0, ""),
  IF(after = "", "No invoice", TEXTBEFORE(after, " ", 1, 0, 1))
)

=LET(
  lines; A2:A7;
  after; TEXTAFTER(lines; "factuur "; 1; 1; 0; "");
  IF(after = ""; "No invoice"; TEXTBEFORE(after; " "; 1; 0; 1))
)

Result: 25-1101 / 25-1104 / No invoice / 25-1103 / No invoice / 25-110625-1101 / 25-1104 / No invoice / 25-1103 / No invoice / 25-1106

Step by step

LET names the pieces. lines is A2:A7, six bank descriptions. Nothing is calculated yet.
after is TEXTAFTER(lines, "factuur ", 1, 1, 0, ""). The delimiter is the word plus a space. match_mode 1 ignores case, so FACTUUR matches too. if_not_found is an empty text.
Row by row after holds:
- 25-1101 Kaya Design BV
- 25-1104 Hendriks Metaal
- an empty text (Salaris december has no factuur)
- 25-1103
- an empty text (Overboeking spaarrekening)
- 25-1106 Smits Bouw BV
IF(after = "", ...) tests each row first: where after is empty, the label No invoice is shown. For rows 3 and 5, IF returns the label instead of the TEXTBEFORE result.
Otherwise the third argument runs: TEXTBEFORE(after, " ", 1, 0, 1) cuts at the first space and keeps what comes before. That gives 25-1101, 25-1104, 25-1103 and 25-1106. In 25-1103 there is no space, and match_end 1 lets the end of the text act as the delimiter, so the number stays whole.
Six answers spill from D2 to D7: 25-1101, 25-1104, No invoice, 25-1103, No invoice, 25-1106.

Watch out: The mirror-image mistake is leaving match_mode at its default 0. FACTUUR 25-1104 is then not found, the row shows No invoice, and the result looks plausible because other rows have the label too.

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

Why it works

Splitting the job into after and the final IF lets the test on after, not on the final result, decide which rows have no invoice, and makes IF return the label instead of the TEXTBEFORE result for those rows; that is why if_not_found is an empty text and not the label itself, because TEXTBEFORE would otherwise cut the label into pieces.

Do not use this construction when descriptions follow no pattern (references with several numbers, typos in the word factuur): TEXTAFTER finds only the exact delimiter, so in that case Power Query or a manual review is more reliable, at the cost of a refresh step or time.

More to watch out for

If a description reads Factuur 25-1106, Smits Bouw BV, the cut at the first space leaves a comma behind: 25-1106,. Split on a comma as well with an array constant, {" ",","}, or check the result with LEN.
Leave out the sixth argument of TEXTAFTER and every line without factuur gives #N/A, which then flows through the IF test. Only those rows become #N/A; the other rows stay correct.

Variations

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

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

Without LET: shorter, but lines without factuur then show #N/A instead of a label (TEXTBEFORE passes the error on), and a reviewer sees two nested calls without names.

Common mistakes

Common mistakes
Symptom	Cause	Fix
#N/A in some rows, other rows work	Some rows return #N/A while others work.	The delimiter is missing in those cells, or occurs fewer times than instance_num asks. Supply if_not_found to show a message.
A lookup on the result finds nothing	The result looks right but a lookup on it finds nothing.	The delimiter was shorter than the real separator, for example `","` where the text has `", "`, so a space stays in front. Put the space in the delimiter, or wrap the result in TRIM.
A number-like part is left out of a sum	A part that looks like a number, such as `0412`, is returned as text and does not add up.	TEXTAFTER always returns text. Wrap it in VALUE when you need a number; note that VALUE drops leading zeros.
A delimiter in capitals is not found	A delimiter written in capitals is not found, although the word is in the text.	The default is case-sensitive. Set match_mode to 1 to ignore case.

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)