How to Remove Special Characters in Excel (#, @, *, symbols)

Imported data arrives with #, *, (, ), dashes and invisible characters. Pick the method that fits how many different symbols you have.

A few known characters: SUBSTITUTE

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

Each SUBSTITUTE removes one symbol. See SUBSTITUTE.

One-off clean-up: Find and Replace

Ctrl+H › Find what: # › Replace with: leave empty › Replace All. Repeat for each symbol.

* and ? are wildcards. Replacing * with nothing empties every cell! Type ~* and ~? to mean the actual characters. More: Find and Replace.

Invisible characters: CLEAN and CHAR(160)

• =CLEAN(A2) removes line breaks and other non-printing characters.
• Web data often contains a non-breaking space that TRIM misses: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

Keep only letters and numbers (Excel 365)

=LET(t,A2,c,MID(t,SEQUENCE(LEN(t)),1),TEXTJOIN("",TRUE,IF(ISNUMBER(SEARCH(c,"abcdefghijklmnopqrstuvwxyz0123456789 ")),c,"")))

Splits the text into single characters, keeps the ones on the allowed list, joins them back. Add characters to the list to keep them (e.g. “.” or “@”).

Keep only the digits

Same idea with the list “0123456789” — turns “(98) 765-43” into 9876543. Wrap in VALUE for a real number.

Flash Fill

For consistent patterns, type one clean example and press Ctrl+E. See Flash Fill.