Clean Text in Excel with TRIM, CLEAN and SUBSTITUTE

Imported text can look correct while failing to match a formula. Extra spaces and invisible characters can split one category into several groups. A careful cleaning workflow keeps the raw values available so you can explain what changed.

Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.

Create a controlled example

Put Raw label in A1 and Clean label in B1. In A2 type two spaces, then Tech, then two spaces. In A3 type Home. In A4 type Tech without surrounding spaces. Enter =LEN(A2) in C2 and fill down. The lengths should be 8, 4 and 4.

LEN counts characters, including ordinary spaces that are hard to see at the edges. This gives you an objective check rather than relying on appearance. The three rows still represent three records.

Apply a specific cleaning rule

In B2 enter =TRIM(A2) and fill down. The results are Tech, Home and Tech. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces inside text to single spaces.

That internal behaviour matters. Multiple spaces inside a code might be meaningful. Define the field before changing it. A rule appropriate for a simple category label may be inappropriate for an identifier or a carefully formatted note.

Handle a common invisible character

In A5 use ="Tech"&CHAR(160) to create Tech followed by a non-breaking space. TRIM alone does not remove that character. Use =TRIM(SUBSTITUTE(A5,CHAR(160)," ")) in B5 to replace it with an ordinary space before trimming.

CLEAN removes certain nonprinting characters, but not every Unicode invisible character. A combination such as =TRIM(CLEAN(SUBSTITUTE(A5,CHAR(160)," "))) addresses several common import issues without promising universal cleaning.

Do not remove every punctuation mark just because a comparison fails. A hyphen, leading zero or internal space can belong to the actual value. Identify the unwanted character and choose a justified replacement.

Check the summary effect

For A2:A4, =COUNTIF(A2:A4,"Tech") returns 1. Using B2:B4 returns 2 after cleaning. The record count stays three; the category matching changes. This is the intended effect, not evidence that one customer or sale was duplicated.

Review one example of each issue type before copying the cleaned results into a final dataset. Paste values only when you deliberately want to replace formulas with their checked results. Keep the original file and a note describing the cleaning rule.

Try it yourself

Create ="Home"&CHAR(160) in A6. Clean it in B6 and compare the lengths. Why would changing its number format not solve the problem?

Keep exploring: Excel learning path.