Text Functions in Excel: Split, Join and Clean Data

Which function solves which task

Messy text in a spreadsheet usually has one of three problems: extra characters, several values crammed into one cell, or one value spread across several cells. Each has its own tool:

The order matters: clean first, then split or join. Otherwise a stray space turns "Aziz" and "Aziz " into two different values, and lookups and pivot tables stop matching.

The formulas below use a comma as the argument separator. With some regional settings Excel expects a semicolon instead: =LEFT(A2;5).

Cleaning: TRIM, CLEAN and SUBSTITUTE

• TRIM removes leading and trailing spaces and collapses repeated spaces between words into one.
• CLEAN removes non-printing characters such as line breaks and control codes that often arrive with exports from other systems.
• SUBSTITUTE replaces one fragment with another, either everywhere or only the N-th occurrence.

A common trap: text copied from websites contains a non-breaking space (character 160). TRIM does not treat it as a space, so replace it first:

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

Splitting full names

Suppose column A contains "Karimov Aziz Bakhtiyorovich".

TEXTSPLIT (Microsoft 365 and Excel for the web) splits a string by a delimiter and spills the parts into neighboring cells:

=TEXTSPLIT(TRIM(A2), " ")

You get three columns: last name, first name, patronymic. Keep the cells to the right empty, otherwise you will see a #SPILL! error.

In older versions combine LEFT, MID and RIGHT with FIND, which returns the position of a character:

Last name:  =LEFT(A2, FIND(" ", A2) - 1)
First name: =MID(A2, FIND(" ", A2) + 1, FIND(" ", A2, FIND(" ", A2) + 1) - FIND(" ", A2) - 1)
Patronymic: =RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) + 1))

These formulas fail when a name has only two words, so wrap them in IFERROR or check the number of spaces first.

LEFT, MID and RIGHT also shine on fixed-format codes: for SKU-0153-RED, the formula =MID(A2, 5, 4) returns 0153.

Normalizing phone numbers

Phones in a client base usually come in mixed formats: 90 123-45-67, (90) 1234567, +998901234567. To bring them to one format, strip everything except digits, take the last 9 digits and add the country code:

=LET(
  d, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "-", ""), "(", ""), ")", ""), "+", ""),
  "+998" & RIGHT(d, 9)
)

LET is available in Microsoft 365. In older versions put the SUBSTITUTE chain in a helper column. Then check the length: =LEN(B2)=13 quickly reveals numbers that were typed with a missing digit.

Joining: TEXTJOIN and &

TEXTJOIN joins a range with a delimiter and can skip empty cells:

=TEXTJOIN(" ", TRUE, B2:D2)

The second argument TRUE ignores blanks, so a missing patronymic does not leave a double space. For two or three values the & operator is enough: =B2 & " " & LEFT(C2, 1) & "." gives "Karimov A.".

TEXTJOIN is also handy for collecting a list into one cell, for example all team emails separated by "; " to paste into a mail client.

Flash Fill: by example, without formulas

Flash Fill (Data → Flash Fill or Ctrl+E) looks at what you type in the first one or two cells and continues the pattern. Type "Aziz" next to "Karimov Aziz Bakhtiyorovich", press Ctrl+E, and Excel fills first names for the whole column.

How to choose:

• Flash Fill for one-off cleanup. The result is static values: if the source changes, nothing recalculates.
• Formulas when data is updated regularly or comes from imports. The result updates automatically.
• Always review Flash Fill output on irregular rows. It guesses the pattern and can be wrong on exceptions.

Common mistakes

• Splitting before cleaning. Double spaces produce empty columns and shifted data.
• Overwriting the source. Keep the original column and work next to it; convert to values only at the end (Copy → Paste Special → Values).
• Phones stored as numbers. Excel drops the leading "+" and may show long numbers in scientific notation. Store them as text.
• Mixed delimiters. Commas, semicolons and slashes in one column. TEXTSPLIT accepts an array of delimiters: =TEXTSPLIT(A2, {",", ";", "/"}).