Remove all punctuation question

crunchtime9999

Board Regular
Joined
Feb 26, 2007
Messages
214
I have searched the threads on how to remove all punctation from a worksheet. I did not find one that would cycle through all non-empty cells (that I could see) and replace the punctuation with a space. The workbook will have varying amounts of rows and columns.

If someone could help me to locate the code I would appreciate it.

Thanks
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I will not have the file. My users will be receiving order files and I need to remove punctuation to be able to upload the information into FedEx batch labels system. Their system cannot accept puncuation. The users are not so proficient at excel and they would have to make multiple passes using find/replace to remove all possible puncuation that could come in an order file.

If you could help with vba that would be great.

Thanks
 
Upvote 0
Hi,
I read that thread and I don't need the spaces removed. Just the puncuation and replace with a space.

How can I modify this to do that??
 
Upvote 0
I assume you want to leave cells with formulas intact as is.

Perhaps:

Code:
Sub test()
On Error Resume Next
Dim c As Range, y As Range
Set y = Cells.SpecialCells(xlCellTypeConstants)
For Each c In y
    c = RemovePunctuation(c.Text)
Next
End Sub

Function RemovePunctuation(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Z0-9 ]"
    .IgnoreCase = True
    .Global = True
    RemovePunctuation = .Replace(r, " ")
End With
End Function
 
Upvote 0
Hello,
sorry for the late reply as I was out of pocket for a couple of days. I will try this code and see if it works as needed.

I really appreciate the excel lesson (I research what I get here to understand what is being done to understand and learn).

I will post back and let you know if the results turn out.

Thanks again and chat with you soon
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top