How to remove all punctuation marks (comma, quotation, apostrophe) from cells?
When working with data in Excel, you might encounter situations where your cells contain a variety of punctuation marks—such as commas, quotation marks, periods, question marks, colons, and more. These punctuation marks can interfere with data analysis, sorting, text extraction, or formatting. For example, when preparing text for further computation, exporting clean data sets, or standardizing user input, you may want to strip all punctuation leaving only letters, numbers, and possibly spaces.
This article provides several practical solutions to help you efficiently remove all kinds of punctuation marks from cells in Excel. Whether you prefer using a User Defined Function, a dedicated Excel add-in, or native formulas, you’ll find a solution suitable for your scenario:
- Remove all punctuation marks from cells with Excel Formula (no VBA or add-ins)
- Remove all punctuation marks from cells with Kutools for Excel
- Remove all punctuation marks from cells with User Defined Function
Remove all punctuation marks from cells with Excel Formula (no VBA or add-ins)
This solution is ideal for users who cannot use macros or do not have access to Excel add-ins. You can utilize nested Excel formulas to comprehensively remove all punctuation marks from text. This method relies on built-in Excel functions, making it highly portable and compliant with most organizational security policies. Note: While this formula-based approach is robust, it may become complex when handling a wide variety of punctuation and very long strings, and formulas need adjustment if you want to define your own list of punctuation to remove.
Scenario example: You receive a data sheet with product descriptions or names that include commas, periods, question marks, various quotation marks, and you need them all removed for clean reporting or importing into another system.
1. In your target cell (for instance, B2), enter the following formula to remove some of the most common punctuation marks (customize the character list as needed):
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ",", ""), ".", ""), "!", ""), "?", ""), ";", ""), ":", ""), """", "")2. Press Enter to apply the formula. To process more rows, drag the fill handle down to copy the formula for additional cells.
If you need to remove additional punctuation marks, further nest additional SUBSTITUTE functions, replacing the unwanted character in each layer. For more advanced scenarios or when the list of punctuation is long, you can use this TEXTJOIN array-based formula (Excel2016 and later):
=TEXTJOIN("", TRUE, IF(ISNUMBER(FIND(MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1), "~!@#$%^&*()_-+={}[]:;""''<>,./?`\|")), "", MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1)))This formula works as an array formula, removing all characters found in the list ~!@#$%^&*()_-+={}[]:;""''<>,./?`\|. To use this formula:
- Enter it in B2 (or any target cell).
- If you are using Excel365,2019, or2016, simply press Enter. In older versions, you may need to confirm with Ctrl+Shift+Enter to ensure array formula activation.
- Copy down as needed for additional rows.
Notes:
- Update or expand the text in the punctuation list within the
FIND()function as necessary for your specific requirements. - Be careful using quotation marks in the list; double them up inside the string (“” for a single literal “).
- Leading and trailing spaces are preserved; use
TRIM()if you need to remove extra spaces as well. - Array formulas may impact performance with very large data ranges.
Troubleshooting: If your formula outputs an error, ensure the text in A2 contains a value, and that array formulas are entered correctly (check for curly braces { } in older Excel after entering with Ctrl+Shift+Enter). Blank results mean that your cell may only contain punctuation marks or is blank itself.
Remove all punctuation marks from cells with Kutools for Excel
If you often need to clean up text and require a quick, graphical tool, Kutools for Excel’s Remove Characters utility can help remove punctuation marks from any selected range efficiently. Unlike formulas or code, this add-in provides a simple dialog where you directly specify which characters to remove, offering flexibility and ease of use for both basic and advanced users.
1. Select the range you will remove all punctuation marks from, and click Kutools > Text > Remove Characters. See screenshot:
2. In the opening Remove Characters dialog box, only check the Custom option, and type the punctuation marks ~!@#$%^&*()_-+={}[]:;""''<>,./?`\| into the box below. Then click the Ok button to instantly remove these punctuation symbols from all selected cells. See screenshot below:
Review the results immediately in your worksheet. This utility is especially beneficial when cleaning imported data, preparing datasets for analysis, or enforcing consistent text formatting standards. It works directly on your selected cells, so be sure to review affected data before saving.
Demo: Remove all punctuation marks from cells with Kutools for Excel
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Remove all punctuation marks from cells with User Defined Function
This method introduces a User Defined Function (UDF) that allows you to remove any kind of punctuation marks from cells in Excel. UDFs are ideal if you anticipate performing this operation often and are comfortable enabling macros in your workbook. This method lets you handle nearly all punctuation in a customizable way and is especially useful if you want to share or reuse the function quickly.
However, bear in mind that this solution requires macro-enabled workbooks and may be restricted in environments with strict macro policies. Always save your work before running or adding new code, and if you share your workbook, macro compatibility must be considered.
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 Function3. After entering the code, press Ctrl+S to save the User Defined Function and close the Visual Basic for Applications window. Make sure to save your workbook as a Macro-Enabled Workbook (*.xlsm) to ensure the function will work next time.
4. Back in Excel, select a blank cell where you want your result to appear, enter the formula =RemovePunctuation(A2) (where A2 is the cell you wish to clean), and then drag the Fill Handle down as needed to apply to other cells.
After applying the formula, all forms of punctuation marks will be stripped from your selected cells, leaving just the core text and numbers. As shown below:
Tip: For very large data sets, UDFs might take extra time to recalculate. If needed, convert the results to values by copying and pasting the output as values.
Related articles:
How to remove letters from strings/numbers/cells in Excel?
How to remove numbers from text strings in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in