Removing Punctuation Macro Also Removing Leading Zeroes

[dpmaki]

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?

[VBA]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

[/VBA]

[Paul_Hossler]

Might try ..


[VBA]

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

[/VBA]


Paul

[Bob Phillips]

[VBA]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
[/VBA]