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.

Which number (there are three in your example):

118 ?
6 ?
425,000 ?
--
Gary''s Student - gsnu200783


"tipsy" wrote:

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.

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)


--
Biff
Microsoft Excel MVP


"tipsy" wrote in message
...
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.

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" wrote:

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)


--
Biff
Microsoft Excel MVP


"tipsy" wrote in message
...
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.

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.


--
Biff
Microsoft Excel MVP


"tipsy" wrote in message
...
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" wrote:

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)


--
Biff
Microsoft Excel MVP


"tipsy" wrote in message
...
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.