This code will work....
Please note that the IsNumber Function can be credited to Rick Rothstein.
Code:
Sub Test()
Dim rng As Range
Dim cll As Range
Dim IntLp As Integer
Dim strt As Integer
Set rng = Sheets(1).Range("A1:M29")
For Each cll In rng
CllLen = Len(cll)
If CllLen > 0 Then
For IntLp = 1 To CllLen
strt = strt + 1
If IsNumber(Mid(cll, strt, IntLp)) <> True Then
cll.Value = Replace(cll.Value, Mid(cll, strt, IntLp), "")
End If
strt = 0
Next IntLp
End If
Next cll
End Sub
Function IsNumber(ByVal Value As String) As Boolean
' Uncomment the next statement out if you
' want to provide for plus/minus signs
' If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9.]*" And _
Not Value Like "*.*.*" And _
Len(Value) > 0 And Value <> "." And _
Value <> vbNullString
End Function
Bookmarks