Sub GetDigits()

Dim i As Long
Dim mycell As Range
Dim str As String, s As String, res As String

For Each mycell In Selection
str = mycell
For i = 1 To Len(str)
s = Mid(str, i, 1)
res = res & IIf(s Like "[0-9]", s, "")
Next i
mycell = res
res = ""
Next mycell

End Sub

And in case you might want to be flexable about removing either alpha or
numeric in one procedure......

Sub RemoveLettersOrNumbers()

Dim myRange As Range
Dim cell As Range
Dim MyStr As String, Which As String
Dim i As Integer

MsgBox ("This macro will only leave you with numbers or letters it will
not leave characters" & _
" such as !@#$%^&*()-+=\")

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & "," &
Selection.Address).SpecialCells(xlCellTypeConstant s)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
Which = InputBox("Strip Numbers - Enter 1" & vbCrLf & "Strip Letters
- Enter 2")
If Which = 2 Then
For Each cell In myRange
MyStr = cell.text
For i = 1 To Len(MyStr)
If (Asc(UCase(Mid(MyStr, i, 1))) < 48) Or
(Asc(UCase(Mid(MyStr, i, 1))) 57) Then
MyStr = Left(MyStr, i - 1) & " " & Mid(MyStr, i + 1)
End If
Next i
cell.Value = Application.Trim(MyStr)
Next cell
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
ElseIf Which = 1 Then
For Each cell In myRange
MyStr = cell.text
For i = 1 To Len(MyStr)
If (Asc(UCase(Mid(MyStr, i, 1))) < 65) Or
(Asc(UCase(Mid(MyStr, i, 1))) 90) Then
MyStr = Left(MyStr, i - 1) & " " & Mid(MyStr, i + 1)
End If
Next i
cell.Value = Application.Trim(MyStr)
Next cell
End If
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub
--
Mike Q.


"guillermo.ht" wrote:

I have two columns (A:B) of data in my spreadsheet where people enter
telephone numbers. To enforce the integrity of the data in these columns, I
need a macro that will select columns A:B and remove all non-numerical
characters. 0-9 is all I want. I need to remove spaces and the a host of
other things such as dash, slash, dot, and parenthesis.
I have a macro that trims the leading spaces, and runs through a long
find/replace routine, but I wondered if there is a more graceful and better
way to remove everything that isn't a number 0-9 from these range of cells.

For example: 919-123-4567 or 919.123.4567 would become 9191234567

Any suggestions?