Remove all punctuation question

[crunchtime9999]

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

 

[crunchtime9999]

I believe it will be vba with case statements, however I don't know how the code will loop through all cells.

 

[Smitty]

Why not just use Find & Replace?

 

[crunchtime9999]

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

 

[Smitty]

See if this post has what you want.

Unfortunately I'm in a meeting all morning.

 

[crunchtime9999]

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??

 

[Scott Huish]

I assume you want to leave cells with formulas intact as is.

Perhaps:

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

 

[crunchtime9999]

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