Remove Punctuation in Excel — Keep Only Letters and Numbers

When you analyze text data in Excel, you often need to remove punctuation from cells and keep only letters and numbers: before counting words, comparing phrases, finding duplicates or building a keyword list.

Replacing each punctuation character one by one across a range takes a lot of time. One possible solution is bulk replacement of values in Excel, but there is no guarantee it removes all punctuation: your eyes can simply miss some characters, especially in a large dataset. Are there faster ways? Yes, there are!

Remove punctuation with a formula in Excel 365

This formula works in Excel 365 and Excel 2021, which can join an array of characters back into a string. It removes all characters except letters (characters that have an uppercase and a lowercase form), digits and spaces. Each punctuation mark is replaced with a space, and TRIM removes the extra spaces, so “word,word” doesn’t turn into “wordword”.

=TRIM(CONCAT(LET(c,MID(A1,SEQUENCE(LEN(A1)),1),IF(NOT(EXACT(LOWER(c),UPPER(c)))+ISNUMBER(--c)+(c=" "),c," "))))

How it works: MID with SEQUENCE splits the text into characters. A character is kept if it’s a letter (its lowercase and uppercase versions differ, which EXACT checks), a digit (–c turns it into a number) or a space. Everything else becomes a space. Since the letter check relies on case, the formula keeps letters of any alphabet, including é, ü or ñ.

In Excel 2019, which has CONCAT but no LET or SEQUENCE, the same logic looks bulkier and is confirmed with Ctrl+Shift+Enter:

=TRIM(CONCAT(IF(NOT(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))+(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" "),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)," ")))

Note the second row: the formula also splits words with hyphens, “@” and periods inside. If you need to keep certain characters, add them to the condition, for example +(c=”-“) for hyphens.

Remove punctuation using regular expressions

If your words consist only of English letters and digits, the easiest way is regex. More about them in the article on regular expressions in Excel. In short, older Excel versions don’t support them out of the box, but you can connect the library with VBA.

I’ve done that for you: connect the !SEMTools add-in to Excel, and the functions start working. They are available even in the free version of !SEMTools. In Excel 365, REGEXREPLACE is built in.

The regex to remove all characters except letters, digits and spaces:

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

• REGEXREPLACE replaces every character not listed in the square brackets with a space;
• TRIM removes the extra spaces.

In the built-in Excel 365 function, you can use the Unicode classes \p{L} (any letter) and \p{N} (any digit), so letters with diacritics are kept too:

=TRIM(REGEXREPLACE(A1,"[^\p{L}\p{N}\s]"," "))

Remove punctuation in 2 clicks with !SEMTools

Formulas like the ones above have some drawbacks, not critical, but still:

• they take time to write and to understand;
• they need a separate column, which you then fill down, paste as values and delete;
• the regex with [a-zA-Z] removes letters with diacritics unless you list them manually, and there are lots of them. Here are just the variants of the letter A: Ă Ắ Ặ Ằ Ẳ Ẵ Ǎ Â Ấ Ậ Ầ Ẩ Ẫ Ä Ǟ Ȧ Ǡ Ạ Ȁ À Ả Ȃ Ā Ą Å Ǻ Ḁ Ⱥ Ã.

For those who, like me, don’t want to compromise on saving time, I made a !SEMTools procedure that has none of these problems. You can try it in the free version too, but it processes 2/3 of the data.

The algorithm is simple:

• select the column with the source phrases;
• run the procedure from DELETE → Characters → Punctuation;
• done, right in place!

Summary: which way to remove punctuation

I hope you found what you were looking for on this page and were able to remove the punctuation from your cells. In Excel 365, the formula with LET or the regex with \p{L} keeps letters of any language, and in older versions the regex works with !SEMTools. But Excel formulas are hard to remember, and for regular cleanup the !SEMTools procedure does it in a couple of clicks.

You may also be interested in related operations: remove extra spaces, remove numbers from cells, extract only letters or change letter case.