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|Sep|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
