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

[Ron Coderre]

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

[MH]

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

You had already solved your own problem!


"()" <gary.moffat@gmail.com> wrote in message
news:1143054615.700755.91710@j33g2000cwa.googlegroups.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
>

[Duke Carey]

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