TEXTSPLIT TEXTJOIN and Text Cleanup Formulas That Work

Text arrives dirty. A name with two spaces in the middle, a postcode that picked up a non-breaking space from a web page, an address in one cell that you need as three, a list of tags in one cell that should be a row each. Excel has had functions for this since the beginning, and a newer set makes the common jobs much shorter. This article covers TEXTSPLIT, TEXTJOIN, TEXTBEFORE and TEXTAFTER, the cleaning functions that go with them and the fallbacks for older versions. It is part of Excel formulas data people use every week, and the SQL side of the same jobs is in text functions in Excel and SQL.

Clean first, then split

Splitting dirty text splits it wrong, so remove the noise first. A cleaning formula for most imported text:

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

Reading from the inside: SUBSTITUTE turns non-breaking spaces (character 160) into ordinary spaces; CLEAN removes the non-printing control characters from code 0 to 31; TRIM removes leading and trailing spaces and collapses runs of spaces inside to one. Order matters: TRIM cannot see a non-breaking space, so the substitution comes first.

Other invisible characters need explicit handling. The zero-width space (8203), the byte order mark (65279) and the soft hyphen (173) survive all of the above. Remove them with SUBSTITUTE(x, UNICHAR(8203), ""). To see what is hiding, =UNICODE(MID(A2, n, 1)) shows the code of the nth character; a string that looks like ABC but has length 4 holds one you cannot see (trimming spaces and non breaking spaces goes through diagnosis).

TEXTSPLIT

=TEXTSPLIT(A2, ",")

For a,b,c in A2, this spills a, b, c across three cells. The second argument is the column delimiter; the third is an optional row delimiter, which spills down:

=TEXTSPLIT(A2, ",", ";")

For a,b;c,d this returns a two by two block. Delimiters can be arrays: TEXTSPLIT(A2, {",", ";", " "}) splits on any of the three. Useful arguments after those: ignore_empty (TRUE skips empty pieces, so a,,b gives two pieces rather than three), match_mode (1 for case-insensitive delimiters) and pad_with (what to fill short rows with, instead of #N/A).

Trim the pieces, since spaces around delimiters survive: =TRIM(TEXTSPLIT(A2, ",")) works because TRIM accepts an array. To turn a delimited list into one value per row, use the row delimiter, or TOCOL(TEXTSPLIT(A2, ",")).

The SQL form is string_to_array with unnest in PostgreSQL, or regexp_split_to_table. In SQL it is the right tool when the lists are in a column of a large table (splitting text by delimiter in Excel and SQL).

TEXTBEFORE and TEXTAFTER

For one piece, these are clearer than a split:

=TEXTBEFORE(A2, "@")
=TEXTAFTER(A2, "@")

For [email protected] they return ana and example.com. An instance number picks the nth occurrence, and a negative number counts from the end: TEXTAFTER(A2, "/", -1) returns the last path segment of a URL. The last argument supplies a value when the delimiter is not found: TEXTBEFORE(A2, "@", 1, 0, 0, "no at sign"), which beats a wrapper of IFERROR.

Use them to take a domain from an email, a file name from a path, a first word from a sentence (TEXTBEFORE(A2, " ")) and a code from inside brackets (TEXTBEFORE(TEXTAFTER(A2, "["), "]")).

TEXTJOIN

=TEXTJOIN(", ", TRUE, A2:E2)

joins the cells with a comma and space. The second argument is ignore_empty: with TRUE, blank cells are skipped and you get no doubled separators. CONCATENATE and the & operator cannot skip blanks, which is why an address built with & ends up with , , when the second line is empty.

TEXTJOIN joins any range or array, including the output of other functions:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B100, A2:A100="North")))

gives the distinct customers in the North region as one string. This is the formula form of string_agg(DISTINCT customer, ', ') in PostgreSQL. A cell holds at most 32,767 characters, so a very long join returns #VALUE!.

For a conditional join in older versions, TEXTJOIN with an array expression needs entering as an array formula. In Microsoft 365 it works as typed.

Fixed-width and positional pieces

Not all text has delimiters. A code like UK-2026-00417 has fixed parts:

=LEFT(A2, 2)          -> UK
=MID(A2, 4, 4)        -> 2026
=RIGHT(A2, 5)         -> 00417

MID(text, start, length) counts from 1. When positions vary, find the delimiter first: =MID(A2, FIND("-", A2) + 1, 4). FIND is case-sensitive and errors when the text is absent; SEARCH is case-insensitive, accepts wildcards and also errors. Wrap in IFERROR only where "not present" is a legitimate case.

Numbers carrying formatting need care. =VALUE(SUBSTITUTE(A2, ",", "")) turns "1,250" into 1250; =--SUBSTITUTE(A2, "$", "") removes a currency symbol first. If the thousands separator and the decimal mark differ by locale (1.250,50), swap them in the right order or use NUMBERVALUE(A2, ",", "."), which takes the decimal and group separators as arguments and ignores locale.

Case and padding

UPPER, LOWER and PROPER change case. PROPER("o'brien") gives O'Brien but PROPER("mcdonald") gives Mcdonald, and PROPER("USA") gives Usa. It is a quick fix for plain names and a poor one for anything with convention. TEXT(A2, "00000") pads a number to five digits and RIGHT("00000" & A2, 5) pads a text value; neither can recover digits already lost (keeping leading zeros in Excel).

SUBSTITUTE(text, old, new, [instance]) replaces every occurrence, or only the nth. REPLACE(text, start, length, new) replaces by position. To remove all spaces: SUBSTITUTE(A2, " ", ""). To count occurrences of a character: LEN(A2) - LEN(SUBSTITUTE(A2, ",", "")).

Older versions

Without TEXTSPLIT, TEXTBEFORE and TEXTAFTER (Excel 2019 and earlier), combine the classics:

Before the first comma:  =LEFT(A2, FIND(",", A2 & ",") - 1)
After the first comma:   =TRIM(MID(A2, FIND(",", A2 & ",") + 1, LEN(A2)))
Nth piece of a list:     =TRIM(MID(SUBSTITUTE(A2, ",", REPT(" ", 100)), (N-1)*100 + 1, 100))

The A2 & "," trick appends a delimiter so that FIND always succeeds; the repeated-spaces trick pads every delimiter to a wide gap so that MID can pick the nth piece by position. They work and they are unpleasant to read, which is a reason to use the new functions when everyone has them. Or use Flash Fill (Ctrl+E), which infers a split from examples, though it produces values and not formulas and does not update.

Doing it in SQL or Power Query instead

A cleanup you do once on a small sheet suits formulas. One that repeats with every import suits Power Query, where Split Column by Delimiter, Trim and Clean are recorded steps that refresh (splitting and merging columns in Power Query). One that runs over millions of rows suits SQL, with trim, split_part, regexp_replace and string_agg. Formulas also hide their rules in cells; a query or a recorded step shows them in order.

When text formulas are the wrong cleanup tool

They are the wrong tool for names, addresses and free text with no consistent structure: a formula splits on a delimiter and cannot understand that "Smith, John" and "John Smith" are the same person. They are wrong when the cleanup must be auditable and repeatable, because a column of formulas does not record why a rule exists. And they are wrong for data volume: thousands of TEXTSPLIT calls recalculate on every change. For name and address splitting see splitting full names and addresses in Excel, and for the function details, the Microsoft pages for TEXTSPLIT and TEXTJOIN.