Display numbers only, no text formula

[Mira]

Is there a formula that will pull the numbers only from
the letters and numbers mixed in?
Example: D16M644HGUD


Thank you!

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=SUM((--MID(0&A1,LARGE(IF(ISNUMBER(-MID(0&A1,seq,1)),seq,1),seq),1))*10
^(seq-1))

where seq is a defined name with the formula
seq: =ROW(INDIRECT("1:255"))

Not sure about the originator of this formula. My guess would be Harlan
or Aladin?

 

[Gord Dibben]

Mira

Can you use VBA?

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