30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Remove Unwanted Characters in Excel (Formula)
By Puneet Gogia (Microsoft MVP)

How to Remove Unwanted Characters in Excel (Formula)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To remove an unwanted character from a cell, use =SUBSTITUTE(A1,"@","") — the empty quotes at the end mean “replace it with nothing”. For two or three characters, nest SUBSTITUTE inside itself: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"@",""),"!",""),"-","").

Once the list gets longer than that, the nesting becomes unmanageable, and Excel 365 gives you two better options. To strip everything except a set of characters, use REGEXREPLACE — =REGEXREPLACE(A1,"[^A-Za-z0-9 ]","") removes every symbol and leaves letters, numbers and spaces, and =REGEXREPLACE(A1,"[^0-9]","")+0 keeps only the digits and turns the result back into a number.

To remove non-printing characters that came from a web page or PDF, use =TRIM(CLEAN(A1)), since those are invisible and won’t match anything you type into SUBSTITUTE. Remember SUBSTITUTE is case-sensitive — a rule written for “A” won’t touch a lowercase “a”.

In Excel, while working with data, you might face some characters you don’t want. In this case, you can write a formula using the SUBSTITUTE function to remove these unwanted characters. And in this tutorial, we will learn to write this formula.

Formula to Remove Unwanted Characters

Follow these steps to write this formula:

  1. First, in a cell, enter the SUBSTITUTE function.
  2. Next, refer to the cell where you have the original text.
  3. After that, enter the character you want to remove (use the double quotation marks).
  4. Now, enter the replacement in double quotation marks: a space (” “) or an empty value (“”) to remove the character completely.
  5. In the end, hit Enter to get the result.
remove-unwanted-characters
=SUBSTITUTE(A1,"@"," ")

This formula removes the “@” character from the value in cell A1 and replaces it with a blank space. And if you don’t want anything in its place, use an empty value (“”) instead of the space, like in the following formula.

substitute-to-remove-unwanted-character
=SUBSTITUTE(A1,"@","")

Using the SUBSTITUTE function is the quickest way to remove unwanted characters from a string. And if you want to remove two or more different characters from a single cell, then you need to nest one SUBSTITUTE function for each character.

remove-more-than-one-unwanted-character

In the above example, you have three different characters that we don’t want to have in the text string. So now, you need to use a nested SUBSTITUTE to eliminate these characters.

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

You have three SUBSTITUTE functions; the formula removes one unwanted character with each function.

Keep in mind that SUBSTITUTE is case-sensitive. So, SUBSTITUTE(A1,”x”,””) removes a lowercase “x” but leaves an uppercase “X”. To remove both, nest two SUBSTITUTE functions, one for each case.

Remove Many Characters with One Formula

When you have a long list of characters to remove, nesting SUBSTITUTE gets hard to read. In Microsoft 365, you can put the characters in an array and let REDUCE run SUBSTITUTE for each one:

=REDUCE(A2,{"@","!","-"},LAMBDA(a,c,SUBSTITUTE(a,c,"")))

To remove another character, just add it to the list in the curly brackets.

Remove Characters You Can’t See

Data copied from websites or other systems often has characters you can’t see, like line breaks, other non-printing characters, and non-breaking spaces (CHAR(160)). CLEAN removes the non-printing characters, but not the non-breaking space, so replace that one with a normal space first:

=CLEAN(SUBSTITUTE(A1,CHAR(160)," "))

To remove extra spaces as well, wrap the formula in TRIM. You can learn more about it in this guide on how to remove extra spaces.

Remove Characters without a Formula

If you don’t need to keep the original data, use Find and Replace. Select the cells, press Ctrl + H, type the character in “Find what”, leave “Replace with” empty, and click Replace All. Just note that Find and Replace treats * and ? as wildcards, so to remove an asterisk, type ~* in “Find what”.

Frequently Asked Questions

How do I remove only the first occurrence of a character?

Use the fourth argument of SUBSTITUTE (instance_num). With 1, it replaces only the first “@” and leaves the others:

=SUBSTITUTE(A1,"@","",1)

How do I remove characters from the start or end of a text?

When the position matters more than the character, use RIGHT or LEFT with LEN. See how to remove the first character and remove the last character.

Why does my number stay as text after removing characters?

SUBSTITUTE always returns text, even when the result looks like a number. Wrap the formula in VALUE, or add –, to turn the result into a number you can calculate with.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment