Need Help Removing all Letters Leaving only Numbers

[Sam]

I need a Macro that will strip all letters and leave only numbers in a
column. The below Macro works fine but it also strips out any leading
zeros "0" (0123) returns (123) which is not good. Can any-one help?

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String


Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)


For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR


End Sub

 

[Sox]

One change: Instead of initializing

strTemp = ""

initilaize it as a leading apostrophe, so Excel knows it is supposed to be
text. :

strTemp = "'"

 

[Sam]

Works Great......... Thank you

 

[Sox]

Works Great......... Thank you

You're welcome.

It was obvious the basic subroutine was doing what it was supposed to
do...so the next step for any Microsoft product is to check ways it tries to
"help" you by doing some annoying automatic task. Like removing leading
zeros. Other ways to do that, of course, such as formatting the cells to
include leading zeros (but that assumes all the resulting numbers should be
the same length, and it wasn't clear how you felt about two or three or more
leading zeros as opposed to however many the text in the cell had to begin
with).