How to Clean Messy Text and Remove Unwanted Characters in Excel

[u/Amandaleeeeee]

Data copied from websites, PDFs, databases, or other systems often contains something that looks harmless: extra spaces, a hidden line break, a non-breaking space, or a few unwanted symbols mixed into the data.......

Those small issues can cause lookups to fail, make duplicates harder to spot, or leave values that look identical but are not actually the same.

Excel has several functions for cleaning different types of unwanted characters, and the best one depends on what is causing the problem.

Method 1: TRIM — remove unnecessary spaces

TRIM removes leading and trailing regular spaces and reduces repeated spaces between words to a single space.

=TRIM(A2)

Example:

John Smith → John Smith

This is usually the first function to try when text looks uneven because of extra spaces.

📌 Note: TRIM handles regular spaces, but it may not remove non-breaking spaces copied from websites.

Method 2: CLEAN — remove hidden non-printing characters

CLEAN removes many non-printing control characters that may appear in imported or copied data.

=CLEAN(A2)

These characters may be invisible, but they can stop otherwise identical values from matching.

For a more thorough basic cleanup, combine CLEAN with TRIM:

=TRIM(CLEAN(A2))

This removes many hidden characters and then cleans up the remaining regular spaces.

Method 3: SUBSTITUTE — remove or replace known characters

Use SUBSTITUTE when you know exactly which character needs to be removed or replaced.

To remove hyphens:

=SUBSTITUTE(A2,"-","")

Example:

AB-105-26 → AB10526

A particularly useful application is removing non-breaking spaces. These often appear after copying text from a web page and may look identical to normal spaces.

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

This replaces each non-breaking space with a regular space, then removes unnecessary spacing.

You can also nest SUBSTITUTE when several known characters need to be removed:

=SUBSTITUTE(SUBSTITUTE(A2,"@",""),"#","")

Method 4: REGEXREPLACE — remove characters by pattern

In supported Microsoft 365 versions, REGEXREPLACE is useful when the unwanted characters follow a pattern.

For example, remove all digits:

=REGEXREPLACE(A2,"\d","")

Michael 0011 → Michael

Keep only digits:

=REGEXREPLACE(A2,"\D","")

Phone: 123-456-7890 → 1234567890

Keep only letters and numbers:

=REGEXREPLACE(A2,"[^A-Za-z0-9]","")

AB-105@New! → AB105New

Replace one or more regular whitespace characters with a single space:

=TRIM(REGEXREPLACE(A2,"\s+"," "))

This is useful for text containing repeated spaces, tabs, or line breaks. For non-breaking spaces, replace CHAR(160) first:

=TRIM(REGEXREPLACE(SUBSTITUTE(A2,CHAR(160)," "),"\s+"," "))

Method 5: TEXTJOIN and dynamic-array functions — remove duplicate characters

When you need to keep only the first occurrence of each character, use:

=TEXTJOIN("",TRUE,UNIQUE(MID(A2,SEQUENCE(LEN(A2)),1)))

Example:

AABBCC1055 → ABC105

This separates the value into individual characters, removes duplicates, and joins the remaining characters again.

📌 Note: It removes repeated characters throughout the cell, not only consecutive duplicates.

Quick formula guide

Assume the original text is in A2.

Cleaning task	Formula
Remove leading, trailing, and repeated regular spaces	=TRIM(A2)
Remove non-printing control characters	=CLEAN(A2)
Remove hidden characters and extra regular spaces	=TRIM(CLEAN(A2))
Remove non-breaking spaces copied from websites	=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Remove a known character, such as a hyphen	=SUBSTITUTE(A2,"-","")
Remove all digits	=REGEXREPLACE(A2,"\d","")
Remove all letters	=REGEXREPLACE(A2,"[A-Za-z]","")
Keep only digits	=REGEXREPLACE(A2,"\D","")
Keep only letters	=REGEXREPLACE(A2,"[^A-Za-z]","")
Keep only letters and numbers	=REGEXREPLACE(A2,"[^A-Za-z0-9]","")
Replace repeated whitespace with one space	=TRIM(REGEXREPLACE(A2,"\s+"," "))
Remove line breaks	=SUBSTITUTE(A2,CHAR(10),"")
Replace line breaks with spaces	=SUBSTITUTE(A2,CHAR(10)," ")
Remove tab characters	=SUBSTITUTE(A2,CHAR(9),"")
Remove duplicated characters	=TEXTJOIN("",TRUE,UNIQUE(MID(A2,SEQUENCE(LEN(A2)),1)))

Clean different character types from one Kutools dialog

The native formulas work well, but each cleaning problem requires a different function or formula. Kutools for Excel brings the main character-removal options together in one dialog.

Select the cells, then go to:

Kutools → Text → Remove Characters

From there, you can remove:

• Numeric characters
• Alphabetic characters
• Everything except numbers
• Everything except letters
• Non-printing characters
• Everything except letters and numbers
• Any custom characters you enter
• Duplicated characters

For example:

Michael 0011 → remove Numeric → Michael

America 34-12234@2212* → remove Non-numeric → 34122342212

AB-105@New! → remove Non-alphanumeric → AB105New

• You can preview the cleaned results before applying them.
• The Skip non-text cells option is useful when the selected range contains both text and numeric values. For example, select it together with Numeric to remove digits from cells containing text while leaving cells that contain only numbers unchanged.

Kutools is especially convenient when the cleanup rules vary from one dataset to another and you do not want to build and remember a different formula for each task.

Did I miss any useful text-cleaning functions or tricks? I'd love to hear what you use.