Cannot format data after using mid() function
 

Have a strange problem after doing the following with Excel 2000 on a
PC:
1. split up cell using text-to-column on: "(391.281, 15.220)"
2. Use MID(), e.g. "=MID(A11,2,8)" to strip off the parenthesis on
"(391.281"
I use Num_chars=8 because that is the maximum string length I
encounter

After that the cells containing those values cannot be reformatted to a
number by any menu or toolbar actions, and there is no warning that it
does not succeed.

Only thing that works is to apply "=VALUE()" to the result.

Is this expected behavior?
Thanks

Cannot format data after using mid() function
 

Yes....The MID function converts its first argument to text and returns a
value AS text. To have a number returned, you need to either use the MID
fundtion result in a numeric function OR....the generally accepted method is
to use a double-negative operator:

=--MID(A11,2,8)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"()" wrote:

Have a strange problem after doing the following with Excel 2000 on a
PC:
1. split up cell using text-to-column on: "(391.281, 15.220)"
2. Use MID(), e.g. "=MID(A11,2,8)" to strip off the parenthesis on
"(391.281"
I use Num_chars=8 because that is the maximum string length I
encounter

After that the cells containing those values cannot be reformatted to a
number by any menu or toolbar actions, and there is no warning that it
does not succeed.

Only thing that works is to apply "=VALUE()" to the result.

Is this expected behavior?
Thanks

Cannot format data after using mid() function
 

Yep, MID() returns a string, not a number. Use the function
=Value(Mid(A11,2,8))

You had already solved your own problem!


"()" wrote in message
ups.com...
Have a strange problem after doing the following with Excel 2000 on a
PC:
1. split up cell using text-to-column on: "(391.281, 15.220)"
2. Use MID(), e.g. "=MID(A11,2,8)" to strip off the parenthesis on
"(391.281"
I use Num_chars=8 because that is the maximum string length I
encounter

After that the cells containing those values cannot be reformatted to a
number by any menu or toolbar actions, and there is no warning that it
does not succeed.

Only thing that works is to apply "=VALUE()" to the result.

Is this expected behavior?
Thanks

Cannot format data after using mid() function
 

In addition to Ron's & MH's answers, you could do a search & replace BEFORE
the Text-to-Columns, ridding the data of the open & close parens. Then the
text-to-columns would likely give you #s instead of text


"()" wrote:

Have a strange problem after doing the following with Excel 2000 on a
PC:
1. split up cell using text-to-column on: "(391.281, 15.220)"
2. Use MID(), e.g. "=MID(A11,2,8)" to strip off the parenthesis on
"(391.281"
I use Num_chars=8 because that is the maximum string length I
encounter

After that the cells containing those values cannot be reformatted to a
number by any menu or toolbar actions, and there is no warning that it
does not succeed.

Only thing that works is to apply "=VALUE()" to the result.

Is this expected behavior?
Thanks