Remove Special Characters

What it does

You have a column of text with garbage characters: emoji, smart quotes, copyright symbols, stray non-printable characters from a web scrape. The macro asks which characters to keep (alphanumeric only, alphanumeric plus spaces, or alphanumeric plus basic punctuation), then strips everything else from each text cell in your selection. Formula cells are skipped. The result is text you can dedupe, lookup, and compare without weird-character mismatches.

Real-world example

Picture a digital marketing analyst at a regional restaurant chain. Every Monday she imports the prior week's customer review text from a third-party reputation platform. The reviews come in with users' display names attached, and those display names contain everything the human imagination can produce: emoji, hearts, country flags, smart-quoted nicknames, a few that are entirely non-Latin scripts. She needs to dedupe customers across weeks to track repeat reviewers, but Maria 🌮 and Maria won't match in a VLOOKUP, and "Bob the Builder" (with curly quotes) won't match "Bob the Builder" (with straight quotes).

Her current method: a chain of Find/Replace operations, one per problem character. Replace 🌮, replace 🍕, replace ❤️, replace ', replace ', replace ", replace ". About 8 minutes per week, and she misses new emoji every week because the menu of human creativity expands monthly.

With this macro she selects the display name column, runs the tool, picks 2 (alphanumeric plus spaces), and 2,300 rows go from emoji-laden chaos to clean text in 1 second. The popup confirms how many cells got modified. Her dedupe immediately works, and the report to her boss this week shows 312 repeat reviewers instead of the 218 she was finding before because the matching is now consistent.

On a 2,300-row weekly review export, expect about 7 minutes saved per pass. Across a year of weekly imports that's nearly 6 hours back. The bigger win is consistent dedupe matches that don't depend on her remembering every problem character.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range of text cells you want to clean.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick RemoveSpecialCharacters, click Run.
3. Type 1 to keep only letters and digits, 2 to also keep spaces, or 3 to also keep periods, hyphens, and underscores.
4. The macro modifies every text cell in place. The popup reports modified and skipped counts.

FAQ

What characters does mode 1 keep?

Letters a through z (case insensitive) and digits 0 through 9. Everything else, including spaces, gets stripped. So Maria Lopez 🌮 becomes MariaLopez. Use this mode when you need a tight identifier without any whitespace.

What characters does mode 3 add to mode 2?

Period (.), hyphen (-), and underscore (_). These three are commonly meaningful in IDs, file names, and SKUs. So SKU-1234.v2 survives mode 3 unchanged but gets stripped to SKU1234v2 in mode 1. If your data needs other punctuation preserved (like + or /), edit the .bas source — it's three lines to add a character to the allowed set.

Will it touch numbers or dates?

No. The macro only processes cells whose value is a text string. Numbers and dates are skipped. So if your column has Maria next to 2026-04-28, only the name cell gets touched; the date stays as is.

Will it skip formulas?

Yes. Formula cells are skipped to avoid silently corrupting calculations. The popup tells you how many were skipped so you can decide whether to freeze the formulas first with Convert Formulas to Values and re-run.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes every modification. As always with bulk text rewrites, work on a copy of your data first if it's the only copy. The macro runs entirely offline. Your data never leaves your machine.