Excel Prg to retreive numeric values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Suppose Cell A1 has "TWID10293 Comments" entry an
cell A2 has "TW298382 Comment on this." entry
I wish to retrieve the numeric part of A1 and A2 in B1 and B2 respectively. ie B1 should have "10293" and B2 should have "29382".
 
If you wish to retain the original data, copy Column A to B then run this
macro on Column B.

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

Gord Dibben Excel MVP
 
Back
Top