Does anyone have any suggestions on how to retrieve values within cells?
In cell A1, there is a text as shown below
Indices September 14, 2008 | ABCD: 101.9 | POIUYT: 70.06 |
I would like to retrieve the date September 14, 2008 into cell B1, and
the value 101.9 in cell C1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

=MID(A1,FIND(" ",A1)+1,FIND("|",A1)-FIND(" ",A1)-2)
=MID(A1,FIND(":",A1)+2,FIND("|",MID(A1,FIND(":",A1 )+1,999))-3)
--
David Biddulph

"Eric" wrote in message
...
Does anyone have any suggestions on how to retrieve values within cells?
In cell A1, there is a text as shown below
Indices September 14, 2008 | ABCD: 101.9 | POIUYT: 70.06 |
I would like to retrieve the date September 14, 2008 into cell B1, and
the value 101.9 in cell C1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

Try this

B1 put this formula =MID(LEFT(A1,FIND("|",A1)-1),FIND(" ",A1),255)

in C1 put this formula = =MID(A1,FIND(":",A1)+1,FIND(":",A1)-
FIND("|",A1))


On Sep 14, 6:33*pm, Eric wrote:
Does anyone have any suggestions on how to retrieve values within cells?
In cell A1, there is a text as shown below
Indices September 14, 2008 | ABCD: 101.9 | POIUYT: 70.06 |
I would like to retrieve the date September 14, 2008 into cell B1, and
the value 101.9 in cell C1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

That works in that particular case, but that C1 formula doesn't cope with
varying lengths of the string instead of 101.9.
I think it needs to be a bit more complicated, see my reply earlier.
--
David Biddulph

"muddan madhu" wrote in message
...
Try this

B1 put this formula =MID(LEFT(A1,FIND("|",A1)-1),FIND(" ",A1),255)

in C1 put this formula = =MID(A1,FIND(":",A1)+1,FIND(":",A1)-
FIND("|",A1))


On Sep 14, 6:33 pm, Eric wrote:
Does anyone have any suggestions on how to retrieve values within cells?
In cell A1, there is a text as shown below
Indices September 14, 2008 | ABCD: 101.9 | POIUYT: 70.06 |
I would like to retrieve the date September 14, 2008 into cell B1, and
the value 101.9 in cell C1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

yah,

just now tested with different conditions, I think ur formula works
fine

On Sep 14, 7:30*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
That works in that particular case, but that C1 formula doesn't cope with
varying lengths of the string instead of 101.9.
I think it needs to be a bit more complicated, see my reply earlier.
--
David Biddulph

"muddan madhu" wrote in message

...
Try this

B1 put this formula =MID(LEFT(A1,FIND("|",A1)-1),FIND(" ",A1),255)

in C1 put this formula = =MID(A1,FIND(":",A1)+1,FIND(":",A1)-
FIND("|",A1))

On Sep 14, 6:33 pm, Eric wrote:

Does anyone have any suggestions on how to retrieve values within cells?
In cell A1, there is a text as shown below
Indices September 14, 2008 | ABCD: 101.9 | POIUYT: 70.06 |
I would like to retrieve the date September 14, 2008 into cell B1, and
the value 101.9 in cell C1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

DataText to ColumnsPipe De-limited

At step 3 Skip the columns you don't need to import.


Gord Dibben MS Excel MVP


On Sun, 14 Sep 2008 06:33:01 -0700, Eric
wrote:

Does anyone have any suggestions on how to retrieve values within cells?
In cell A1, there is a text as shown below
Indices September 14, 2008 | ABCD: 101.9 | POIUYT: 70.06 |
I would like to retrieve the date September 14, 2008 into cell B1, and
the value 101.9 in cell C1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

On Sun, 14 Sep 2008 06:33:01 -0700, Eric
wrote:

Does anyone have any suggestions on how to retrieve values within cells?
In cell A1, there is a text as shown below
Indices September 14, 2008 | ABCD: 101.9 | POIUYT: 70.06 |
I would like to retrieve the date September 14, 2008 into cell B1, and
the value 101.9 in cell C1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

It can certainly be done.

How best to do it depends on how well your single example is representative of
what might be in the cell.

It's fairly simple if you download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr, and then use regular expressions to extract
what you want.

For example, if all of your dates are in the format you show above, and end
with a four digit year, you could use:

=REGEX.MID(A1,"(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Se p|Oct|Nov|Dec).*\d{4}")

If the value you wish to extract is the first numeric value that follows a
colon (:), you could use:

=--TRIM(REGEX.MID(A1,"(?<=:)\s*[-+]?\b\d*\.?\d+\b"))

If you also want to extract the ABCD: into another cell, you could use:

=REGEX.MID(A1,"\b[A-Za-z]+:") to include the colon, or, if you just want ABCD,
try: =REGEX.MID(A1,"\b[A-Za-z]+(?=:)")

If you also want to gete the POIUYT and the 70.06, there is an optional "index"
argument in the REGEX.MID function. Just use a 2 for that argument:

=--TRIM(REGEX.MID(A1,"(?<=:)\s*[-+]?\b\d*\.?\d+\b",2))
=REGEX.MID(A1,"\b[A-Za-z]+(?=:)",2)

--ron