What counts as a special character
In everyday spreadsheet work, a “special character” is anything that is not a letter, a digit or a space: punctuation such as # * ( ) and !, symbols such as ™ ✓ ★ and ₹, typographic dashes and quotes, and emoji. They arrive when text is copied from websites, PDFs, WhatsApp messages or online forms, and they cause real trouble later. Lookups fail because “Coffee #1” does not match “Coffee 1”, imports reject fields with unexpected symbols, and phone numbers with spaces and dashes cannot be dialled or matched by another system.
How to remove special characters manually in Excel
Find and Replace
Press Ctrl+H, type one character in Find what, leave Replace with empty and click Replace All. Repeat for every character. Two characters need care: * and ? are wildcards, so search for ~* and ~? instead, otherwise Excel replaces everything. This works for a known handful of symbols, but not when you do not know which ones are in the file.
SUBSTITUTE
Nest one SUBSTITUTE per character in a helper column:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"#",""),"*",""),"™","")
Then copy the helper column and use Home › Paste › Paste Values over the original. The formula grows quickly and still misses any symbol you did not list.
CLEAN does not do this
=CLEAN(A2) is often suggested, but it only removes the invisible control characters with codes 0 to 31, such as line breaks from old systems. It leaves #, ™, emoji and every other visible symbol in place.
REGEXREPLACE in Microsoft 365
Recent Microsoft 365 builds (from 2024 onwards) include regular-expression functions. To keep letters, digits and spaces:
=REGEXREPLACE(A2,"[^\p{L}\p{M}\p{N} ]","")
The \p{M} part matters. Many guides use [^\p{L}\p{N} ], which looks right for English but deletes the combining marks that Hindi, Gujarati, Tamil and other Indic scripts use for vowel signs, so “शर्मा” falls apart into disconnected letters. REGEXREPLACE is not available in Excel 2016, 2019, 2021 or older Microsoft 365 builds.
Power Query
In Power Query, add a custom column such as:
Text.Select([Product], {"a".."z", "A".."Z", "0".."9", " "})
It is reliable for plain English text, but the ranges only cover unaccented Latin letters. “José” becomes “Jos” and names in Devanagari or Gujarati disappear entirely.
LAMBDA and TEXTJOIN
It is possible to split a cell into characters with MID and SEQUENCE, test each one and join the survivors with TEXTJOIN. These formulas work in Microsoft 365, but they are long, hard to check and usually based on character codes that again only recognise English letters.
Where this tool helps
Product catalogues before an import. Names like “Tea – Masala (500g)™” or “Coffee #1 *Best*” are rejected or mangled by Shopify, Tally and many ERP imports. Keeping letters, numbers and spaces gives “Tea Masala 500g” and “Coffee 1 Best”.
Phone numbers. Choose “Numbers only” and turn off “Keep spaces between words”, and “+91 98765-43210”, “(022) 2345 6789” and “98765 43210” become plain digits such as 919876543210, ready for an SMS platform or for matching against another list. Add + to the allowlist to keep the country-code sign.
Names from web forms. Customers add emoji, decorative quotes and stray dots to their names. Cleaning them makes mail merges and printed labels look right, while names in Hindi, Gujarati or with accents such as “José Álvarez” stay exactly as entered.
Lookup keys. Before a VLOOKUP or XLOOKUP between two lists, removing punctuation from codes and names on both sides makes matches far more likely.
How the tool handles your text
- Letters in every script are kept, with their accents and vowel signs. This is the main difference from most regex snippets and online cleaners, which treat only A to Z as letters.
- Digits in any script count as numbers, so Devanagari or Gujarati numerals are kept too.
- Your allowlist wins. Anything typed into “Also keep these characters” stays, so you can keep hyphens in account codes, dots in decimals or @ in email addresses.
- Spaces are tidied. With “Keep spaces between words” on, runs of spaces left behind collapse into one and the ends are trimmed. Turn it off to squeeze values into a single run of characters.
- Only text cells change. Numbers, dates, true/false values and headers are never touched, and a cell left with nothing becomes empty.
The summary tells you exactly what happened, for example “Removed 39 characters from 13 cells, keeping letters, numbers and spaces.” A CSV comes back as a UTF-8 CSV that Excel opens correctly, and an Excel file comes back as .xlsx.
A good order for cleaning
Remove special characters first, then remove extra spaces and fix the case of names. Finally run Remove duplicates: once symbols, spacing and capitals agree, records that were really the same become easy to spot.