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

Picking part of a cell based on cell contents
 

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

Picking part of a cell based on cell contents
 

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

Picking part of a cell based on cell contents
 

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

Picking part of a cell based on cell contents
 

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.

Picking part of a cell based on cell contents
 

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

Picking part of a cell based on cell contents
 

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

Picking part of a cell based on cell contents
 

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

Thanks