removing text from a string

[Lucas Karpiuk]

how would i go about removing anything from a string that isn't a number,
including spaces, punctuation, etc etc?

thanks in advance

 

[Greg]

Something like this:

Sub StripNonNumerics()
Dim oRng As Range
Dim oChar As Range
Dim tempString As String
Set oRng = Selection.Range

For Each oChar In oRng.Characters
If Asc(oChar) > 47 And Asc(oChar) < 58 Then
tempString = tempString + oChar
End If
Next
Selection.Range = tempString
End Sub

 

[Greg]

Lucas,

I didn't do a very good job with my first reply. You want to strip
nonnumerics from a string not a range. Try:

Sub StripNonNumericsFromString()
Dim oString As String
Dim i As Long
Dim cleanString As String
oString = "abc123efg"
For i = 1 To Len(oString)
If Asc(Mid(oString, i, 1)) > 47 And Asc(Mid(oString, i, 1)) < 58 Then
cleanString = cleanString + (Mid(oString, i, 1))
End If
Next
MsgBox cleanString
End Sub

 

[Lucas Karpiuk]

Greg,

Actually I didn't word myself properly, I do indeed intend to use this on
selected text - and it works wonderfully, btw.

Thanks so much!