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 this

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

It's all one line.

Mike

" 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

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&"0 123456789")),ROW($1:$10000))))

It's all one line.

Mike

" 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

On Thu, 24 Jul 2008 03:46:07 -0700 (PDT), 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

On Thu, 24 Jul 2008 12:33:28 GMT, Lars-Åke Aspelin
wrote:

On Thu, 24 Jul 2008 03:46:07 -0700 (PDT), 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.

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


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

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).

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

Thanks