Remove Punctuation in Excel — Keep Only Letters and Numbers

When working with textual data in Excel, it’s often necessary to remove punctuation from cells and leave only letters and numbers.

Manually replacing every punctuation character across a range can be extremely time-consuming.

One possible solution is bulk value replacement in Excel. But this approach doesn’t guarantee complete cleanup — you might overlook symbols, especially in large datasets.

This process can be complicated and inefficient. Are there faster ways to remove punctuation? Yes — there are!

Formula for Excel 365

This solution only works in recent Excel versions that support the TEXTJOIN function, which allows concatenating array results. The formula removes everything except characters that:

• change case (uppercase/lowercase),
• are digits,
• or are spaces.

Here’s the formula:

=TEXTJOIN("",TRUE,IF(EXACT(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))+ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

Remove Punctuation Using Regular Expressions

If you’re only interested in preserving Latin/Cyrillic letters and digits — regex is your best friend.

Learn more in the article on regular expressions in Excel. Note: native Excel doesn’t support them out of the box, but you can add regex capabilities via VBA or simply use !SEMTools.

This functionality is available even in the free version of !SEMTools.

To remove all characters except letters, digits, and spaces, use this regex-based formula:

=TRIM(REGEXREPLACE(A1,"[^\sa-zA-Z0-9]"," "))

How this works:

1. REGEXREPLACE replaces everything that is not a space, letter, or digit with a space;
2. TRIM removes extra spaces.

Remove Punctuation in One Click

While formulas are powerful, they have a few downsides:

• They take time to write and understand,
• They usually require an extra column,
• RegEx formulas remove accented or diacritic characters (unless you manually whitelist them — which is a huge list: Ă Ắ Ặ Ằ Ẳ Ẵ Ǎ Â Ấ Ậ Ầ Ẩ Ẫ Ä Ǟ Ȧ Ǡ Ạ Ȁ À Ả Ȃ Ā Ą Å Ǻ Ḁ Ⱥ Ã and so on).

That’s why I created a dedicated procedure in the !SEMTools add-in — to remove punctuation reliably, quickly, and without compromise.

The free version will process up to two-thirds of your data, enough to test the feature thoroughly.

Here’s how it works:

1. Select the column with the original phrases,
2. From the DELETE → Characters → Punctuation menu, run the macro,
3. Done!

Conclusion

Excel formulas can solve a wide range of problems — but it’s hard to remember them all. The !SEMTools add-in makes it easy to remove unwanted characters in Excel and tackle hundreds of similar tasks with just a few clicks.

This post is also available in RU.