How to Remove Special Characters in Excel

The "emoji and weird symbols in my data" problem

You have a column of customer names. Some have hearts, smiley faces, country flags, smart-quoted nicknames, accented characters, and a few entries entirely in non-Latin scripts. Your dedup matches Maria 🌮 against Maria and fails. Your downstream system rejects the file because it expects ASCII.

TL;DR — Key takeaways

• =CLEAN() strips ASCII control characters but leaves emoji and accented letters alone.
• Nested =SUBSTITUTE() can remove specific characters but you have to type each one.
• A VBA macro keeps only allowed characters (alphanumeric, optionally spaces, optionally basic punctuation) and drops everything else.

Method 1: =CLEAN function

=CLEAN(A2)

CLEAN removes ASCII characters 0 through 31 (control characters: tabs, newlines, etc.). It does NOT remove emoji, smart quotes, or accented letters because those are higher in the character range.

The catch: narrow scope. Only handles the most basic invisible characters.

Method 2: Nested SUBSTITUTE

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "🌮", ""), "🍕", ""), "❤️", "")

Type each character to remove. Workable for a known set of characters. Painful when the set grows.

The catch: new characters in the data require new SUBSTITUTE levels. Not maintainable.

Method 3: =CODE inspection

=CODE(A2)

Returns the ASCII code of the first character of the cell. Useful for diagnosing weird characters one at a time. Not a removal method.

Method 4: The free VBA macro

Download Remove Special Characters. Free .xlsm with one macro.

1. Select the range.
2. Alt + F8, pick the macro, click Run.
3. Pick a mode:
   • 1 = letters and digits only.
   • 2 = letters, digits, and spaces.
   • 3 = letters, digits, spaces, and basic punctuation (. - _).
4. Macro strips everything else from each text cell.

In place. One pass. No emoji-by-emoji typing.

What "alphanumeric" means here

The macro keeps:

• Letters a through z (lowercase and uppercase).
• Digits 0 through 9.

Everything else is stripped: emoji, currency symbols, smart quotes, accented letters (café becomes caf), non-Latin scripts.

If you need to keep accented characters, the macro is too aggressive. Edit the .bas source: in the StripChars function, add a case for the unicode range you need.

A common scenario: pre-dedupe normalization

Marketing analyst dedups a weekly review export from a third-party platform. Reviewer names contain emoji, smart quotes, hearts.

1. Select the name column.
2. Run Remove Special Characters. Pick mode 2 (letters, digits, spaces).
3. Now Maria 🌮 and Maria both become Maria and dedup correctly.

Frequently asked questions

Will it remove accented characters like é, ñ, ö?

Yes, in the default modes. The macro keeps only ASCII a-z, A-Z, 0-9. To preserve accents, edit the .bas source.

What about non-Latin scripts (Cyrillic, Arabic, Chinese)?

Stripped. The macro keeps only ASCII letters and digits.

Will it skip formulas?

Yes. Formula cells are skipped. Convert formulas to values first if you need to clean formula results.

Will it remove emoji?

Yes. Emoji are non-ASCII characters and get stripped.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

What to do next

After stripping special characters, common follow-ups: Trim Whitespace From All Cells to fix leftover whitespace from removed characters, Remove Line Breaks From Cells for embedded newlines.