I have been using these two functions for years to clean up data:
Function StripNumeric(Text As String) As String
' Removes all Numbers from a text string
' and returns the remaining text characters
Dim sTemp As String, i As Integer
sTemp = Text
For i = 0 To 9
sTemp = Application.Substitute(sTemp, i, "")
Next i
RemoveNumeric = sTemp
End Function
THis is the one you need
Function KeepNumeric(Text As String) As String
' Removes all text characters from a text string
' and returns the remaining numbers as a text string
Dim sTemp As String, i As Integer
sTemp = Text
For i = 1 To 255
If i < 48 Or i > 57 Then
sTemp = Application.Substitute(sTemp, Chr(i), "")
End If
Next i
KeepNumeric = sTemp
End Function
Thanks,
Mike
Post by Charles Chickering=MID(A1,2,3)&MID(A1,7,3)&MID(A1,11,4)&RIGHT(A1,4)
Sub StripNum()
Dim str As String
Dim cnt As Long
On Error Resume Next
For cnt = 1 To Len(ActiveCell.Value)
str = str & CDbl(Mid(ActiveCell.Value, cnt, 1))
Next
On Error GoTo 0
Debug.Print CDbl(str)
End Sub
Let me know if you need help applying either one
--
Charles Chickering
"A good example is twice the value of good advice."
Post by childofthe1980s(555) 555-1212 Ext. 0000
How do you make that to be 55555512120000?
Thanks!
childofthe1980s