Formula for finding text

[ukdane]

Quick question.

If I have a cell (A3) which reports the string "Something 30 Something 40".
The words "something" are variables so can be of any length, what formula would I use to report the number after the first space (in this instance 30) and what would I use to show after the second space (40)? The numbers 30 and 40 are just examples, and could be any number between 1 and 99999.

Just to make it a bit more complicated, is there an additional solution if the "Something" strings contain spaces as well?

The only constants are the fact that the maximum length of the string is 83 characters

Is there anyway that a formula can filter and report numbers only?

(In the same way that Trim removes blank spaces at the start of a string).

[georgiboy]

I have found and used this function in the past, not sure if it can be adapted to your needs but here it is anyway...

Function Extract_Number_from_Text(Phrase As String) As Double
Dim Length_of_String As Integer
Dim Current_Pos As Integer
Dim Temp As String
Length_of_String = Len(Phrase)
Temp = ""
For Current_Pos = 1 To Length_of_String
If (Mid(Phrase, Current_Pos, 1) = "-") Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (Mid(Phrase, Current_Pos, 1) = ".") Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (IsNumeric(Mid(Phrase, Current_Pos, 1))) = True Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
Next Current_Pos
If Len(Temp) = 0 Then
Extract_Number_from_Text = 0
Else
Extract_Number_from_Text = CDbl(Temp)
End If
End Function

Hope this helps

[ukdane]

Thanks, but I need to avoid using a macro if possible, and stick to a formula.

A Macro/VBA code would be the last resort.

[georgiboy]

This is a function that you would use as formulae in excel frontend.
You paste the code into a module and then use it as formulae. ie...

=Extract_Number_from_Text(A1)

[Bob Phillips]

B1: =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))

C1: =LOOKUP(99^99,--("0"&MID(SUBSTITUTE(A1,B1,""),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,B1,"")&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))