Easiest way to remove text from a cell that has text and numbers?

[cram.it.clownie]

Hi,

I'm just trying to figure out the easiest way to just pull the text out
of cells that have both text and numbers to leave just the number. (the
letters are indiscriminately placed in the cell so I can't just chop
off the beginning or end). I could do multiple incarnations of
Selection.Replace like this:

Selection.Replace What:="A", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="B", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="C", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="D", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
etc. to Z

But I thought I'd ask to see if someone could provide me with a more
elegant solution (like using an array). I googled for a better solution
but to no avail.

tia

 

[Pete_UK]

Using a loop, look at each character of the string - if it is 0 to 9
then add it to a replacement string.

You may also need to consider what to do with symbols, particularly the
full stop and space.

Hope this helps.

Pete

 

[Gord Dibben]

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points
' if don't want decimals, delete the decimal pt. from "[0-9.]" befroe running
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 MS Excel MVP