Gord Dibben said:
Copy the entire column and run this macro on that copy.
OR run it on the original column if you don't care to preserve the alphas.
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
....
Inefficient. Also questionable including commas.
First pass, simplify the If block, access the range's .Value property only
once, and call Mid only once per iteration.
For Each r In rng
v = r.Value
t = ""
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[0-9.]" Then t = t & c
Next i
r.Value = t
Next r
Second pass, eliminate the 1-char temp variable.
For Each r In rng
v = r.Value
For i = 1 To Len(v)
If Mid(v, i, 1) Like "[!0-9.]" Then Mid(v, i, 1) = " "
Next i
r.Value = Application.WorksheetFunction.Substitute(v, " ", "")
Next r
But for maximum flexibility on systems with Windows Script Host installed
(so 99.44% of PCs running Windows 98SE or 2K or later, and most running
Windows NT4), nothing beats regular expressions.
Sub foo()
Dim rng As Range, c As Range, re As Object
Set rng = Selection.SpecialCells( _
Type:=xlCellTypeConstants, _
Value:=xlTextValues _
)
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[^0-9.]+" 'or use an InputBox to set
re.Global = True
For Each c In rng
c.Formula = re.Replace(c.Formula, "")
Next c
End Sub