Picking part of a cell based on cell contents

Hi
I have a cell in Excel which contains the following string

Avg:91.14,Std:0.96,Min:89.5,Max:94.2

How do I pick out just the Avg value (i.e. 91.14) and place that in
another cell?
I would like to do this for multiple cells.
The problem I'm running into is that sometime the Avg value >99.99
(i.e 6 characters as opposed to 5). This means I can't use the
=mid(text,start,characters)
forumla as the characters might be 5 if less than 100 and 6 if 100 or
greater.

Is there a way I could get Excel to recognise the ":" and "," and give
me everything in between?

ps. in another cell I'd also like to pull out the Std value.

Thanks in advance

MikeH
Thu Jul 24 06:07:00 CDT 2008

Try this

=LOOKUP(10^23,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

It's all one line.

Mike

"a.t.brooks@talk21.com" wrote:

> Hi
> I have a cell in Excel which contains the following string
>
> Avg:91.14,Std:0.96,Min:89.5,Max:94.2
>
> How do I pick out just the Avg value (i.e. 91.14) and place that in
> another cell?
> I would like to do this for multiple cells.
> The problem I'm running into is that sometime the Avg value >99.99
> (i.e 6 characters as opposed to 5). This means I can't use the
> =mid(text,start,characters)
> forumla as the characters might be 5 if less than 100 and 6 if 100 or
> greater.
>
> Is there a way I could get Excel to recognise the ":" and "," and give
> me everything in between?
>
> ps. in another cell I'd also like to pull out the Std value.
>
> Thanks in advance
>

MikeH
Thu Jul 24 06:58:00 CDT 2008

I missed you wanted to extract Std as well. Try this

=MID(A1,FIND(CHAR(7),SUBSTITUTE(A1,":",CHAR(7),2))+1,FIND(CHAR(7),SUBSTITUTE(A1,",",CHAR(7),2))-FIND(CHAR(7),SUBSTITUTE(A1,":",CHAR(7),2))-1)

Once again all one line

Mike

"Mike H" wrote:

> Try this
>
> =LOOKUP(10^23,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))
>
> It's all one line.
>
> Mike
>
> "a.t.brooks@talk21.com" wrote:
>
> > Hi
> > I have a cell in Excel which contains the following string
> >
> > Avg:91.14,Std:0.96,Min:89.5,Max:94.2
> >
> > How do I pick out just the Avg value (i.e. 91.14) and place that in
> > another cell?
> > I would like to do this for multiple cells.
> > The problem I'm running into is that sometime the Avg value >99.99
> > (i.e 6 characters as opposed to 5). This means I can't use the
> > =mid(text,start,characters)
> > forumla as the characters might be 5 if less than 100 and 6 if 100 or
> > greater.
> >
> > Is there a way I could get Excel to recognise the ":" and "," and give
> > me everything in between?
> >
> > ps. in another cell I'd also like to pull out the Std value.
> >
> > Thanks in advance
> >

Lars-Åke
Thu Jul 24 07:33:28 CDT 2008

On Thu, 24 Jul 2008 03:46:07 -0700 (PDT), a.t.brooks@talk21.com wrote:

>Hi
>I have a cell in Excel which contains the following string
>
>Avg:91.14,Std:0.96,Min:89.5,Max:94.2
>
>How do I pick out just the Avg value (i.e. 91.14) and place that in
>another cell?
>I would like to do this for multiple cells.
>The problem I'm running into is that sometime the Avg value >99.99
>(i.e 6 characters as opposed to 5). This means I can't use the
>=mid(text,start,characters)
>forumla as the characters might be 5 if less than 100 and 6 if 100 or
>greater.
>
>Is there a way I could get Excel to recognise the ":" and "," and give
>me everything in between?
>
>ps. in another cell I'd also like to pull out the Std value.
>
>Thanks in advance


Try these:

=MID(A1,FIND("Avg:",A1)+4,FIND(",Std",A1)-FIND("Avg:",A1)-4)

and

=MID(A1,FIND("Std:",A1)+4,FIND(",Min",A1)-FIND("Std:",A1)-4)


Hope this helps / Lars-Åke

Lars-Åke
Thu Jul 24 07:37:08 CDT 2008

On Thu, 24 Jul 2008 12:33:28 GMT, Lars-Åke Aspelin
<larske@REMOOOVE.telia.com> wrote:

>On Thu, 24 Jul 2008 03:46:07 -0700 (PDT), a.t.brooks@talk21.com wrote:
>
>>Hi
>>I have a cell in Excel which contains the following string
>>
>>Avg:91.14,Std:0.96,Min:89.5,Max:94.2
>>
>>How do I pick out just the Avg value (i.e. 91.14) and place that in
>>another cell?
>>I would like to do this for multiple cells.
>>The problem I'm running into is that sometime the Avg value >99.99
>>(i.e 6 characters as opposed to 5). This means I can't use the
>>=mid(text,start,characters)
>>forumla as the characters might be 5 if less than 100 and 6 if 100 or
>>greater.
>>
>>Is there a way I could get Excel to recognise the ":" and "," and give
>>me everything in between?
>>
>>ps. in another cell I'd also like to pull out the Std value.
>>
>>Thanks in advance
>
>
>Try these:
>
>=MID(A1,FIND("Avg:",A1)+4,FIND(",Std",A1)-FIND("Avg:",A1)-4)
>
>and
>
>=MID(A1,FIND("Std:",A1)+4,FIND(",Min",A1)-FIND("Std:",A1)-4)
>
>
>Hope this helps / Lars-Åke
>
>
>

And if you have any special formating for numbers that you would like
these result to obey, just add *1 (or +0) at the end of the formulas.

Rick
Thu Jul 24 11:25:25 CDT 2008

Here is how to find all the numbers...

Avg: =RIGHT(LEFT(A1,FIND(",",A1)-1),FIND(":",A1)+1)

Std: =RIGHT(LEFT(A1,FIND(",Min",A1)-1),FIND(":",A1))

Min: =RIGHT(LEFT(A1,FIND(",Max",A1)-1),FIND(":",A1))

Max: =MID(A1,FIND("Max:",A1)+4,99)

Rick


<a.t.brooks@talk21.com> wrote in message
news:96b975b3-d090-4b2f-9741-8b566de5c25e@59g2000hsb.googlegroups.com...
> Hi
> I have a cell in Excel which contains the following string
>
> Avg:91.14,Std:0.96,Min:89.5,Max:94.2
>
> How do I pick out just the Avg value (i.e. 91.14) and place that in
> another cell?
> I would like to do this for multiple cells.
> The problem I'm running into is that sometime the Avg value >99.99
> (i.e 6 characters as opposed to 5). This means I can't use the
> =mid(text,start,characters)
> forumla as the characters might be 5 if less than 100 and 6 if 100 or
> greater.
>
> Is there a way I could get Excel to recognise the ":" and "," and give
> me everything in between?
>
> ps. in another cell I'd also like to pull out the Std value.
>
> Thanks in advance

a
Fri Jul 25 06:08:59 CDT 2008

Thanks
I've got it working now. Now I just have to figure out how to apply
Conditional Formatting on those numbers (Excel still thinks they're
text, so when it doesn't work).

a
Fri Jul 25 06:19:32 CDT 2008

Just realised I can use the =VALUE(A1) to convert to number then
conditional format!

Thanks