🧹 Data Cleaning · 8 min read

Remove Special Characters in Excel: 6 Formulas That Actually Work

There is no single button for this. TRIM removes only the space character, CLEAN removes only invisible control characters, SUBSTITUTE removes characters you name one at a time, and REDUCE with LAMBDA or REGEXREPLACE removes a whole set in one pass. Pick the wrong one and it looks like nothing happened — which is the entire reason this problem is so tedious.

The complaint is almost always the same shape, and it shows up in Excel forums in exactly these words: “How to get rid of weird symbols in Excel?” and “How do I remove special character from phone number in Excel? I use TRIM but it doesn’t work as it is not a space.”

That second sentence is the key to the whole thing. TRIM is not broken. It is doing exactly what it was built to do, on a character that is not the one in your cell.

Why TRIM leaves your spaces behind

TRIM removes the space character (code 32 in the ASCII table) from the start and end of a string, and collapses runs of them in the middle. That is all it does.

Text copied from a web page, an email or a PDF rarely uses that character. It uses the non-breaking space, code 160, which renders identically on screen and is a completely different character as far as Excel is concerned. TRIM walks straight past it, and the cell still fails a lookup that should have matched.

The fix is a nested pair, in this order: convert, then trim.

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

If the text came through a PDF or a web scrape, there may also be a line break or a tab in it. CLEAN deals with those, so the belt-and-braces version is =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))). Nesting them in the wrong order (trimming before substituting) leaves exactly the problem you started with.

What each function actually removes

Nothing in that table removes a set of symbols, which is what people actually want. The rest of this guide is the four ways to get there.

Method 1: SUBSTITUTE, for the characters you can name

If the problem is a short, known list — brackets, slashes, hyphens, currency signs — chain SUBSTITUTE calls. Each one replaces a single string with an empty string:

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

It is readable, it works in every version of Excel, and it is honest about what it does. The limit is obvious: it removes only what you remembered to list. A hidden character you never typed will sail through untouched.

Method 2: CLEAN, for the invisible ones

CLEAN is aimed at the characters you cannot see, and it is well worth knowing its boundary. Microsoft’s own documentation is explicit: CLEAN removes the first 32 non-printing characters of the 7-bit ASCII set, and it does not remove characters that are not part of that set, including the non-breaking space.

So CLEAN is the right tool for text scraped from the web that arrives with line breaks or tabs embedded in it, and the wrong tool for symbols. Use it in combination, not alone.

Method 3: the TEXTJOIN and MID array formula (works without 365)

This is the classic, and it is still the right answer on Excel 2019 and 2021. It walks the cell one character at a time, keeps the characters that are allowed, and joins the survivors back together:

=TEXTJOIN("",TRUE,
  IF(ISNUMBER(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),
     "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ")),
     MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

Enter it with Ctrl+Shift+Enter on versions before dynamic arrays, and copy it down. The long string of allowed characters is your allowlist: add punctuation to it if you want to keep some, and everything else is deleted. It is ugly to read, which is why the newer alternatives below exist.

Method 4: REDUCE with LAMBDA (Microsoft 365)

REDUCE runs a function over a list and carries a running result, which makes it a clean fit for “remove each of these characters in turn”. The list in curly braces is the denylist:

=REDUCE(A2, {"(",")","-","/",".","#","$","%","&","*"}, LAMBDA(acc,ch, SUBSTITUTE(acc,ch,"")))

It is readable, easy to extend and needs no array entry. The catch is availability: LAMBDA and REDUCE are Microsoft 365 features, so a file built on them will not work for a colleague on Excel 2019.

Method 5: REGEXREPLACE, if you are on Microsoft 365 (2024 or later)

Regular expressions were added to Excel in 2024, and they collapse this whole problem into one formula. Negated character classes do the work: this pattern keeps letters, digits and spaces and deletes everything else.

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

-- digits only, for a phone number or an ID:
=REGEXREPLACE(A2, "[^0-9]", "")

-- strip non-ASCII characters, e.g. from scraped text:
=REGEXREPLACE(A2, "[^\x00-\x7F]", "")

One warning on portability: Google Sheets also has REGEXREPLACE and always has, but it runs on the RE2 engine, which has no lookbehind and no backreferences. A pattern that works in Excel may fail there, and vice versa. If the formula has to survive a move between the two, test it in both.

Method 6: Power Query, or clean it outside Excel entirely

When the cleaning is a recurring job rather than a one-off, formulas are the wrong layer. Power Query records the steps (trim, remove characters, change type) and replays them on next month’s file with one click. It handles the non-breaking space and the control characters through its own Transform options, without you writing a formula at all.

And once the file is past Excel’s 1,048,576-row limit, the question changes. Excel truncates a larger CSV, so a column of formulas over a truncated file produces silently wrong answers. Above that line, the cleaning has to happen where the file is: a script, a query over the file itself, or a tool that reads the CSV without laying it out in a grid.

Removing accented letters (é, ö, ñ)

This is a separate problem and Excel has no built-in answer for it. There is no shipped function that folds é to e. A NORMALIZE function has been proposed for years but is not in the product.

The workable approaches are: a chain of SUBSTITUTE calls for the accents that actually occur in your data, a small two-column lookup table fed through REDUCE and LAMBDA, or Power Query, which can do the same fold without a formula. If the accents only matter for a one-off match, cleaning in a script or an online tool is usually quicker than building the chain by hand.

Finding out what a character actually is

When a cell refuses to match and you cannot see why, stop guessing and read the code point. These two formulas will tell you exactly what is in the cell:

=UNICODE(MID(A2,1,1))    -- code point of the first character
=CODE(A2)                -- numeric code of the first character (legacy set)

-- check every character in one go (dynamic arrays):
=TEXTJOIN(",",TRUE,UNICODE(MID(A2,SEQUENCE(LEN(A2)),1)))

A code of 32 is an ordinary space, 160 is a non-breaking space, 9 is a tab and 10 is a line feed. Once you know the number, the fix is a single SUBSTITUTE or CHAR, and the irritation of “I can’t see the problem” disappears.

Which formula to use

Frequently asked questions

What is the fastest way to remove special characters in Excel?

On Microsoft 365, =REGEXREPLACE(A2,"[^A-Za-z0-9 ]","") in a single pass. On older versions, the TEXTJOIN and MID array formula, or a chain of SUBSTITUTE for the characters you can name.

Why doesn’t TRIM remove all the spaces in my cell?

TRIM only removes the space character, code 32. Text pasted from a web page usually contains the non-breaking space, code 160, which looks the same but is a different character. Convert it first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

What does the CLEAN function remove?

The first 32 non-printing characters of the 7-bit ASCII set — codes 0 to 31, which covers line breaks, tabs and control characters. It does not remove accented letters, emoji, currency symbols or the non-breaking space.

How do I remove special characters from an entire column?

Write the formula against the first row, then double-click the fill handle to copy it down. To keep the values and drop the formulas, copy the helper column and paste it back with Paste Special → Values. On Excel 365, one formula can spill over the whole range.

How do I remove accented characters such as é and ö?

There is no built-in transliteration function. Use a SUBSTITUTE chain for the accents that occur in your data, a lookup table with REDUCE and LAMBDA, Power Query, or clean the file in a script or an online tool.

Can I use regular expressions in Excel?

Yes, on Microsoft 365 — REGEXREPLACE and its siblings arrived in 2024. They are absent from Excel 2021, 2019 and perpetual-licence versions, where the array formula or SUBSTITUTE is the fallback. Google Sheets has had RE2-based REGEXREPLACE for years.

How do I clean a file that is too big to open in Excel?

Not with a column of formulas. Excel stops at 1,048,576 rows and truncates larger files, so the cleanup has to happen where the file is — a script, Power Query loaded to the Data Model, or a tool that reads the CSV without rendering it into a grid.

Clean the column, then ask the question

If the reason you are stripping characters is that a lookup, a count or a group-by refuses to match, the cleaning is a means to an end. Once the values are consistent, upload the file and ask the question directly (totals, duplicates, outliers) and get the answer back with a chart, without another formula layer.