Extract a number from a variable text string

[tipsy]

I want to extract a number from a variable text string, ie the number is not
in a fixed position in the text string.

I want the numbers to be placed in a separate column so I can calculate the
median of the series.

E.g.
SB 118 Kenny St BV 6rm $415,000 Stockdale & Leggo Gladstone Park

should return the number 415,000

repeat for each row in the spreadsheet.

Any help appreciated.

 

[Gary''s Student]

Which number (there are three in your example):

118 ?
6 ?
425,000 ?

 

[T. Valko]

With just a single example to go by *maybe* this...

SB 118 Kenny St BV 6rm $415,000 Stockdale & Leggo Gladstone Park

Assuming the number to be extracted is *always* preceded by a $ sign and
followed by a space.

=--LEFT(MID(A1,FIND("$",A1),255),FIND(" ",MID(A1,FIND("$",A1),255))-1)

 

[tipsy]

T.Valko, that works, thank you very much, the assumptions are correct.
I would now like to enhance the formula, such that it ONLY extracts the
number if the first two characters in the string are S , SA or SB.

Is this possible?
Thanks
tipsy

 

[T. Valko]

Try this:

=IF(OR(LEFT(A1,2)={"S ","SA","SB"}),
--LEFT(MID(A1,FIND("$",A1),255),
FIND(" ",MID(A1,FIND("$",A1),255))-1),"")

All on one line.