PDA

View Full Version : Removing Punctuation Macro Also Removing Leading Zeroes



dpmaki
06-14-2012, 10:43 AM
I'm running the following macro to remove unwanted characters, however it's also currently stripping out leading zeroes from any columns that have them. Is there a way around this?

Sub RemovePunctuation()

Worksheets("Data").Activate

With CreateObject("vbscript.regexp")

.Pattern = "[^a-zA-Z^0-9\ ]"

.Global = True

For Each Rng In Selection.SpecialCells(xlCellTypeConstants)

Rng.Value = .Replace(Rng.Value, vbNullString)

Next Rng

End With

End Sub

Paul_Hossler
06-14-2012, 01:10 PM
Might try ..




Rng.NumberFormat = "@"
Rng.Value = .Replace(Rng.Value, vbNullString)




Paul

Bob Phillips
06-14-2012, 01:11 PM
Sub RemovePunctuation()
Dim rng As Range

Worksheets("Data").Activate

With CreateObject("VBScript.Regexp")

.Pattern = "[^a-zA-Z^0-9\ ]"
.Global = True

For Each rng In Selection.SpecialCells(xlCellTypeConstants)

rng.Value = "'" & .Replace(rng.Text, vbNullString)
Next rng
End With
End Sub