Extracting values from strings function

  • Thread starter Thread starter amax
  • Start date Start date
A

amax

Is there any way to extract a number/value from a cell with a string i
it?

For example is there a way that , "Text 1234" could return "1234"

or "tex3t" could return "3"?

I have searched everywhere...I'd be extremely grateful if someone coul
help
 
One way as long as the numbers stick together, i.e. "Text 1234" , not "12
Text 34"

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter
 
amax

Peo has given a worksheet Function method.

If you want to try a macro........

Note: the text will be erased. If you want to keep the original string, make
a copy of the cell(s) before running the macro.

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