Excel Text Functions: Splitting, Extracting and Rebuilding Text Someone Else Typed

A number arrives as a number. Text arrives as whatever the person at the other end felt like typing.

GB-LDN-2291-A is four fields pretending to be one. DOE, john is a name in the wrong order and the wrong case. Replaced filter; order SO-88421 closed 04 Aug is a sentence, and the only part of it you actually want is the eight characters in the middle.

Text functions are how you get the fields back out. Excel ships about thirty of them and you will use nine. This article is about those nine — which one answers which question, which ones only exist in Microsoft 365, and the handful of traps (a space that is not a space, a #VALUE! that means "not found", a split that lands on a row missing a field) that cost an afternoon the first time you meet them.

Version note, read this first. TEXTBEFORE, TEXTAFTER and TEXTSPLIT arrived with Microsoft 365 in 2022 and are also in Excel 2024. They do not exist in Excel 2021, 2019 or earlier — you will get #NAME?. TEXTJOIN and CONCAT go back to Excel 2019. LET needs 2021 or 365. Everything in sections 2, 3, 7, 8 and 9 works in any version this century. Section 10 ends with a table of legacy equivalents, so nothing here is unusable on an older build — it is just longer to write.

1) Every Text Problem Is One of Three Questions

Before reaching for a function, work out which question you are actually asking. There are only three, and each has its own small set of tools.

Most bad text formulas are bad because they answer question two using tools from question one — counting characters to find a boundary that a delimiter already marks. Sections 2 and 4 are that mistake and its fix.

2) LEFT, RIGHT and MID — Counting Characters, and Why It Breaks

The three position-based extractors, all 1-indexed:

• =LEFT(text, n) — first n characters
• =RIGHT(text, n) — last n characters
• =MID(text, start, n) — n characters beginning at position start

🎯 Scenario: Pull the country, city and revision out of the asset tag in column B.

=LEFT(B2, 2)      → GB       the country code is always two characters
=MID(B2, 4, 3)    → LDN      the city code is always three, starting at 4
=RIGHT(B2, 1)     → A        the revision letter is always one... isn't it

The first two are fine. The third is the problem, and it is worth being precise about why.

Run =RIGHT(B4, 1) against T-1043, whose tag is GB-MAN-1150 with no revision letter. It returns 0 — the last digit of the unit number. Not an error, not a blank. A single character that looks exactly like a valid revision code, sitting in a column of valid revision codes, in a report nobody will re-check.

That is the entire case against counting characters. Position formulas are not fragile because they break; they are fragile because they don't. They return a plausible wrong answer and let it travel.

When counting is genuinely correct: the field is fixed-width by specification, not by coincidence. A 2-letter ISO country code, a 13-digit EAN, a 6-digit UK sort code, the year in an ISO date. In every one of those, a value of the wrong length is itself a data error you want to catch.

A cheap check before you trust a position: =MIN(LEN(B2:B9)) and =MAX(LEN(B2:B9)). On this data they return 11 and 13, which tells you in one cell that the tag is not fixed-width and that any RIGHT you write against it is guesswork.

3) FIND and SEARCH — Locating the Delimiter

Both return the position of one string inside another. They differ in three ways that matter:

Both take an optional third argument, start_num, and that is the one people forget:

=FIND("-", B2)          → 3    the first hyphen
=FIND("-", B2, 4)       → 7    the first hyphen at or after position 4
=SEARCH("so-", E2)      → 24   finds "SO-" despite the lower-case needle

Neither returns 0 when the text is absent. They return #VALUE!, which feels hostile until you notice it is the only sensible answer — position zero does not exist — and that it makes a clean test:

=ISNUMBER(SEARCH("backorder", E2))     → TRUE for T-1042, FALSE elsewhere
=IFERROR(FIND("-", B2, 8), LEN(B2)+1)  → "the third hyphen, or just past the end"

That second pattern — substitute a sentinel position when the delimiter is missing — is how position formulas were made survivable before 2022. Here is the classic, extracting the unit number between the second and third hyphen:

=MID(B2,
     FIND("-", B2, 4) + 1,
     FIND("-", B2, FIND("-", B2, 4) + 1) - FIND("-", B2, 4) - 1)

It is correct. It is also three nested FIND calls to say "the bit between the second and third hyphen", and it still throws #VALUE! on T-1043. Read the next section and never write it again.

4) TEXTBEFORE and TEXTAFTER — What LEFT and RIGHT Should Have Been

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

Six arguments, and the last four are the reason these functions are worth learning rather than just "the easy version of LEFT":

• instance_num — which occurrence of the delimiter to cut at. Negative counts from the end: -1 is the last one.
• match_mode — 0 case-sensitive (default), 1 insensitive.
• match_end — 1 treats the end of the text as a delimiter. This is the argument that fixes ragged fields, and almost nobody uses it.
• if_not_found — what to return instead of #N/A. An IFERROR wrapper built into the function, and unlike IFERROR it does not also swallow errors coming from the arguments.

🎯 Scenario: Split the asset tag into country, city, unit and revision — readably, and without breaking on T-1043.

Country   =TEXTBEFORE(B2, "-")                        → GB
City      =TEXTBEFORE(TEXTAFTER(B2,"-",1), "-", 1, 0, 1)   → LDN
Unit      =TEXTBEFORE(TEXTAFTER(B2,"-",2), "-", 1, 0, 1)   → 2291

Follow the city formula through both shapes. TEXTAFTER(B2,"-",1) gives LDN-2291-A for T-1041 and MAN-1150 for T-1043. TEXTBEFORE(..., "-") then cuts at the first hyphen — which works for the first and would throw #N/A for the second, because there is no hyphen left. match_end set to 1 says "if you run out of delimiters, the end of the string counts as one", and MAN-1150 returns MAN instead of an error. One argument, both shapes, no IF.

The revision letter is a different problem: on T-1043 it does not exist at all, so there is nothing for a fallback to return. TEXTAFTER(B4, "-", -1) gives 1150 — the same silent wrong answer as RIGHT, arrived at by a nicer route. You have to count the delimiters:

=LEN(B2) - LEN(SUBSTITUTE(B2, "-", ""))     → 3 for GB-LDN-2291-A, 2 for GB-MAN-1150

Length before, minus length with every hyphen stripped out, is the number of hyphens. It is the oldest trick in the text-function book and still the shortest way to ask "how many fields does this row actually have". Guard on it:

=IF(LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))=3, TEXTAFTER(B2,"-",-1), "")

🎯 Scenario: Pull the order number out of the engineer's free-text note, where it might be anywhere in the sentence or not there at all.

Start with the naive version and watch it fail:

=TEXTAFTER(E2, "SO-")     → 88421 closed 04 Aug

Right delimiter, no right-hand boundary. The order number ends at a space in T-1041, a semicolon in T-1045 and the end of the string in T-1042 — so give TEXTBEFORE all three at once. The delimiter argument accepts an array, which is the feature that makes this tractable:

=LET(
  after, TEXTAFTER(E2, "SO-", 1, 1, 0, ""),
  IF(after = "", "—", "SO-" & TEXTBEFORE(after, {" ", ";", ",", "."}, 1, 0, 1))
)

Reading it: cut after the first SO-, case-insensitively (match_mode 1, so so- in a hurried note still matches), returning empty string rather than #N/A when there is no order at all. Then cut the remainder at whichever of space, semicolon, comma or full stop comes first, with match_end 1 so a note that ends on the number still works. T-1046 has no order and returns an em dash; everything else returns SO-88421 and friends.

Note what this formula does not do: it does not assume the order number is five digits. Hard-coding LEFT(after, 5) would work on all eight rows here and break the first time finance rolls over to six.

5) TEXTSPLIT — One Formula, All the Fields at Once

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

=TEXTSPLIT(B2, "-") spills GB | LDN | 2291 | A across four cells to the right. That is the whole function, and then there are four things to know about it.

It takes one cell, not a range. =TEXTSPLIT(B2:B9, "-") does not split the column — it silently splits B2 and ignores the rest. This is the single most common surprise with the function. To do a whole column you either copy the formula down, or wrap it:

=DROP(REDUCE("", B2:B9, LAMBDA(acc, t, VSTACK(acc, TEXTSPLIT(t, "-")))), 1)

which is a genuinely useful pattern and also the point at which most people go and get Power Query instead.

Ragged rows need pad_with. Stack the split of GB-LDN-2291-A (four parts) under GB-MAN-1150 (three) and the short row is one field light. VSTACK fills the gap with #N/A. Set pad_with to "" and you get blanks instead, which sort, filter and total without complaint.

#SPILL! means the room is occupied. A four-part split needs four empty cells. If anything sits in the way — including a stray space someone typed in 2023 — the whole formula returns #SPILL! rather than a partial result. Click the error triangle and choose Select Obstructing Cells.

The row delimiter turns a string into a grid. The third argument splits downward as well as across:

=TEXTSPLIT("GB,London;DE,Berlin;ES,Madrid", ",", ";")

returns a 3×2 block. This is the fastest way to turn a pasted config line, a log record or a semicolon-delimited email list into a real table.

🎯 Scenario: Break Site Path into city, building and floor, including T-1044 where somebody padded the slashes.

=TRIM(TEXTSPLIT(D2, "/"))

Split first, trim second. TRIM operates over the spilled array in one go, so Salamanca comes back as Salamanca without a helper column. Splitting on " / " instead would have worked for T-1044 and broken every other row — always split on the delimiter itself and clean up afterwards.

6) Putting It Back Together — TEXTJOIN, CONCAT and &

Extraction is half the job; most of these fields are being pulled apart so they can be reassembled in a different shape.

🎯 Scenario: Turn DOE, john into John Doe.

=TRIM(PROPER(TEXTAFTER(C2, ","))) & " " & PROPER(TEXTBEFORE(C2, ","))

TEXTAFTER on the comma returns john — with the space the typist put after the comma — so PROPER capitalises it and TRIM removes the padding before the two halves are joined. Run it against T-1045's doe, JOHN and the trailing space disappears too, because TRIM is doing both jobs at once. PROPER handles the awkward names in this data correctly: O'NEILL becomes O'Neill, Marc-André survives, García Lopez is unchanged.

But know what PROPER breaks. It capitalises the first letter after any non-letter and lower-cases everything else, which means MCDONALD becomes Mcdonald, IBM becomes Ibm, and van der Berg becomes Van Der Berg. There is no fixing this in general — human names do not follow a rule. What you can do is fix the specific cases your data contains, on top of PROPER:

=SUBSTITUTE(SUBSTITUTE(PROPER(C2), "Mcd", "McD"), "O'n", "O'N")

Ugly, explicit, and honest about being a list of exceptions rather than a rule.

TEXTJOIN versus &. The second argument, ignore_empty, is the entire reason to prefer it:

=TEXTJOIN(" / ", TRUE, city, building, floor)

With TRUE, a missing building produces London / Floor 3. With an & chain you get London / / Floor 3, and then a formula to strip the double separator, and then a formula to handle the case where two are missing.

CONCAT versus CONCATENATE. CONCAT accepts ranges (=CONCAT(A2:E2)); CONCATENATE is the legacy version that only takes individual arguments and is kept alive purely for compatibility. There is no reason to type it again.

Numbers lose their formatting the moment they become text. ="Total: " & 1234.5 gives Total: 1234.5, not Total: 1,234.50 — cell formatting is a display property and concatenation reads the underlying value. TEXT is how you carry the format across:

="Invoiced " & TEXT(1234.5, "#,##0.00") & " on " & TEXT(TODAY(), "dd mmm yyyy")

The format codes are the same ones from the Custom number format dialog, so build the format there, copy the code, paste it into TEXT.

7) The Space That Is Not a Space

Two formulas are identical, the cells look identical, and the comparison returns FALSE. This is nearly always an invisible character, and there are three usual suspects.

• TRIM removes leading and trailing spaces and collapses internal runs to a single space. It removes only the standard space, CHAR(32).
• CLEAN removes the first 32 non-printing characters, CHAR(1) to CHAR(31) — line breaks, tabs, form feeds.
• Neither of them removes CHAR(160), the non-breaking space, which is what you get from every copy-paste out of a web page, a PDF or an HTML email. It is the most common invisible character in business data and it is immune to the function everybody reaches for.

Diagnose before you scrub:

=LEN(C6)                 → the true length, spaces included
=LEN(TRIM(C6))           → if this is smaller, ordinary spaces are present
=CODE(RIGHT(C6, 1))      → 32 is a space, 160 is a non-breaking space
=UNICODE(MID(C6, 5, 1))  → for anything above 255, e.g. 8203 = zero-width space

On the sample data, =LEN(C6) returns one more than you would count by eye: T-1045's requester is doe, JOHN with a trailing space, which is why it and T-1041 look like the same person to you and different people to a case-sensitive comparison.

The three-layer scrub, in this order:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

SUBSTITUTE first — it converts the non-breaking spaces into ordinary ones so that TRIM can then see and remove them. Reverse the order and TRIM runs against text that still contains CHAR(160), finds nothing to do at the edges, and you are left with the original problem plus a longer formula.

8) Changing Text — SUBSTITUTE by Content, REPLACE by Position

Two functions, constantly confused, doing genuinely different jobs.

SUBSTITUTE(text, old_text, new_text, [instance_num])   — find this content, swap it
REPLACE(old_text, start_num, num_chars, new_text)      — overwrite this position

SUBSTITUTE replaces every occurrence unless you name one, and it is case-sensitive:

=SUBSTITUTE(B2, "-", "/")        → GB/LDN/2291/A     every hyphen
=SUBSTITUTE(B2, "-", "/", 2)     → GB-LDN/2291-A     the second one only
=SUBSTITUTE(E2, "order", "PO")   → misses "Order" with a capital O

That case sensitivity is a real trap, because SEARCH, COUNTIF and plain = are all case-insensitive. If you need an insensitive substitution, normalise the case first or work through SEARCH and REPLACE.

REPLACE does not care what is there — it overwrites a span. That makes it the right tool for masking:

🎯 Scenario: Show only the last four characters of an account number.

=REPLACE(A2, 1, LEN(A2)-4, REPT("•", LEN(A2)-4))

And the two combined, for "replace the found thing wherever it happens to be":

=REPLACE(E2, SEARCH("order", E2), 5, "PO")

SEARCH finds it case-insensitively, REPLACE overwrites those five characters. This is the standard way to get a case-insensitive substitute out of Excel, and it is worth keeping in a note somewhere because it is not obvious.

9) Comparing Text That Is Not Quite Identical

Three behaviours to have straight, because two of them surprise people in opposite directions.

= ignores case. ="JOHN" = "john" returns TRUE. So do COUNTIF, SUMIFS, MATCH, XLOOKUP and every other lookup in Excel. There is no setting for this.

EXACT respects case. =EXACT("JOHN","john") returns FALSE. It is the only built-in comparison that does.

Neither ignores spaces. ="John " = "John" returns FALSE, and EXACT agrees. Whitespace is real; case is not.

🎯 Scenario: T-1041 and T-1045 are the same engineer visiting the same asset twice — DOE, john and doe, JOHN . Is that one requester or two?

=COUNTIF(C2:C9, C2)                     → 1    the trailing space in C6 defeats it
=COUNTIF(C2:C9, TRIM(C2) & "*")         → 2    wildcard absorbs the padding
=SUMPRODUCT(--EXACT(C2:C9, C2))         → 1    case-sensitive, as intended
=SUMPRODUCT(--(TRIM(C2:C9) = TRIM(C2))) → 2    the answer you probably wanted

Four plausible formulas, three different answers, and the difference is entirely about which invisible property you decided to ignore. Decide deliberately, then write it down in a comment.

Wildcards work in COUNTIF, SUMIFS, MATCH and SEARCH directly; XLOOKUP needs match_mode set to 2 before it will honour them:

=COUNTIF(E2:E9, "*SO-*")                        → 6   notes mentioning an order
=COUNTIF(B2:B9, "GB-*")                         → 4   UK assets
=XLOOKUP("GB-LDN*", B2:B9, A2:A9, "none", 2)    → T-1041

To search for a literal * or ?, escape it with a tilde: "~*". And when you want a substring test rather than a count, ISNUMBER(SEARCH(...)) is the idiom — it is what conditional formatting rules for "contains" compile down to anyway.

10) A Parsing Block That Survives the Next Export

Everything above, assembled into one cell. LET names each step so the formula reads as a short program rather than a wall of nesting, and every extraction has a fallback so a malformed row produces a marker instead of an error cascade.

=LET(
  raw,     $B2,
  tag,     TRIM(CLEAN(SUBSTITUTE(raw, CHAR(160), " "))),
  fields,  LEN(tag) - LEN(SUBSTITUTE(tag, "-", "")) + 1,
  country, TEXTBEFORE(tag, "-", 1, 0, 1, "?"),
  city,    TEXTBEFORE(TEXTAFTER(tag, "-", 1, 0, 0, ""), "-", 1, 0, 1, "?"),
  unit,    TEXTBEFORE(TEXTAFTER(tag, "-", 2, 0, 0, ""), "-", 1, 0, 1, "?"),
  rev,     IF(fields = 4, TEXTAFTER(tag, "-", -1), ""),
  IF(fields < 3,
     "MALFORMED: " & tag,
     HSTACK(country, city, unit, rev))
)

Four things are doing the work here, and each one is a habit worth carrying to the next parsing job:

1. Normalise before you parse. tag is scrubbed once, at the top, and every later step reads the clean version. Parsing raw input and cleaning the pieces afterwards means writing the same TRIM four times and forgetting it once.
2. Count the fields before trusting any of them. fields is computed before a single extraction, and the final IF uses it to reject rows that were never going to parse. A row with two fields gets flagged, not silently truncated.
3. Give every extraction an if_not_found. The "?" markers make a partial failure visible in the output column. Wrapping the whole thing in IFERROR would have hidden which field failed — and would also have swallowed a genuine error in raw.
4. Return a row, not a string. HSTACK spills country, city, unit and revision into four adjacent cells, so downstream formulas get real fields instead of something they have to parse again. (HSTACK is 365-only; on 2021 return TEXTJOIN("|", FALSE, ...) and split it, or just write four formulas.)

Legacy equivalents, for Excel 2021 and earlier

The REPT(" ", 100) trick in row three deserves a word: it replaces every hyphen with a hundred spaces, takes the last hundred characters, and trims. Whatever was after the final hyphen is the only thing that survives. It works for any last-field extraction, it is completely unreadable, and for twenty years it was the answer.

11) Mini Exercises

Copy the grid into a blank sheet at A1 and work down. Answers are all one formula.

1. Country counts. How many assets are in Great Britain? (Two ways: a wildcard COUNTIF, and a SUMPRODUCT over LEFT. They should agree.)
2. The ragged tag. Write one formula in F2, filled down, that returns the revision letter where there is one and "none" where there is not — without using RIGHT.
3. Name repair. Turn column C into Firstname Surname in proper case, with no leading or trailing spaces, in a single formula that also handles T-1045's hidden padding.
4. Order extraction. Return the order number from column E, or "—" when there isn't one, without assuming it is five digits long.
5. Floor number as a number. Site Path ends in Floor n. Return n as a real number you can total — remember VALUE on text that is not numeric returns #VALUE!, so decide what a missing floor should produce.
6. Duplicate visits. Which asset tag appears twice? Get the answer two ways — once case-sensitively with EXACT, once not — and explain to yourself why they agree here and would not if the export had used gb-ldn-2291-a on one row.

Wrap-Up

The functions are easy. The judgement is knowing that RIGHT(tag, 1) is a guess about your data rather than a fact about it, and that the guess will hold until the month it doesn't.

Three habits cover most of it. Scrub before you parse, because CHAR(160) is waiting in every pasted column. Count your delimiters before you trust a position, because a field that is usually four parts will one day be three. And put a fallback on every extraction, because a "?" in a report gets fixed on Tuesday and a plausible wrong value gets fixed in November, by someone else, after the audit.