Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to remove all punctuation marks (comma, quotation, apostrophe) from cells?

For example, there are many punctuation marks existing in cells, such as comma, quota, period, question marks, etc. How could you quickly remove them from cells in Excel? This article provides two solutions as below:

Remove all punctuation marks from cells with User Defined Function

Remove all punctuation marks from cells with Kutools for Excel

Easily remove numbers, letter, or special characters (bullets) from text string in Excel

Kutools for Excel’s Remove Characters utility is designed to remove all letters, all numbers, or special characters (such as bullets) from text strings easily. It’s also able to remove all non-numeric characters, non-alpha characters, or non-alphanumeric characters from specified text strings easily. Full Feature Free Trial 60-day!

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial

This method will introduce a User Defined Function to remove all kinds of punctuation marks from cells in Excel easily. Please do as follows:

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and then copy and paste the following code into the new opening Module window.

VBA: Remove Punctuation marks from cells in Excel

Function RemovePunctuation(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "[^A-Z0-9 ]"
.IgnoreCase = True
.Global = True
RemovePunctuation = .Replace(Txt, "")
End With
End Function

3. Save the User Defined Function and close the Microsoft Visual Basic for Applications window.

4. Select a blank cell you will output result, enter the formula =RemovePunctuation(A2) (A2 is the cell you will remove all punctuation marks from) into it, and then drag the Fill Handle to the range as you need.

doc remove all punctuation 1

And then you will see all kinds of punctuation marks are removed. See screenshot:

doc remove all punctuation 2

Only one click to remove formulas but keep calculated values from multiple cells with Kutools for Excel's To Actual utility!     Free Trial!

ad to actual 2

This method will introduce Kutools for Excel’s Remove Characters utility to quickly remove all kinds of punctuation marks from selected range at once. Please do as follows:

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the range you will remove all punctuation marks from, and click Kutools > Text > Remove Characters. See screenshot:

doc remove all punctuation 01

2. In the opening Remove Characters dialog box, please only check the Custom option, and type the punctuation marks ~!@#$%^&*()_-+={}[]:;""''<>,./?`\| into below box, and click the Ok button. See screenshot:

doc remove all punctuation 4

And then you will see all kinds of punctuation marks are removed from selected cells. See above screenshot.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.