Re: Extract a number from a variable text string
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Sat, 3 May 2008 22:28:24 -0400
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" <tipsy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4716BC87-1B23-4579-A4C8-1F656A7C0A03@xxxxxxxxxxxxxxxx
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" <tipsy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D23C73A5-4861-453D-80A9-BAEB0BDDC348@xxxxxxxxxxxxxxxx
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.
.
- References:
- Extract a number from a variable text string
- From: tipsy
- Re: Extract a number from a variable text string
- From: T. Valko
- Re: Extract a number from a variable text string
- From: tipsy
- Extract a number from a variable text string
- Prev by Date: formula help
- Next by Date: RE: Date calculations
- Previous by thread: Re: Extract a number from a variable text string
- Next by thread: formula help
- Index(es):
Relevant Pages
|
