Excel not recognize numbers

[cadineshkumar]

Dear Members,

I want to apply the following formula in Cell C2 containing AOJPB6685H

=ISNUMBER(MID(C2,6,4)) which resulting in False. While i think that result (6685) should be as True. Can anyone help why excel does not recognize 6685 as number.

[AliGW]

Because the cell contains text, not a mixture of text and numbers.

Try this:

=VALUE(MID(C2,6,4))

[davsth]

or
=ISNUMBER(MID(C2,6,4)*1)

[cadineshkumar]

Thanks for the replies

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Pat and only Pat]

Because the cell contains text,
not a mixture of text and numbers.

No, the true reason is:
because MID returns text
(even if the entire cell is digits).

[Zer0Cool]

As @Pat and only Pat mentions, MID is a text function. Like all text functions, wait for it...it returns text, even if the string is all numeric. ISNUMBER fails because the result returned by MID, despite being all numeric digits, has returned them as text.

As others have pointed out, conversion will need to take place to convert the result to a numeric value. One method is *1 (as any number times 1 is the same number, and Excel is smart enough to know you cant multiply text so it attempts to treat the text as a number. If its all numeric digits then multiplication is possible with the result being a number). Another method is using the VALUE function or the N function. I believe the difference being the VALUE function can convert a wider range of numbers to a wider range of output (like dates, times, etc), however I would imagine for decimal values and integers that N is more efficient.

[Pat and only Pat]

Try this:

=VALUE(MID(C2,6,4))

This works fine where the inspected string -- MID(C2,6,4) -- is indeed a number,
but fails in the other case --
try C2 e.g. as 12345678x0 and you get #VALUE!

davsth got it right, use one of these --

Please Login or Register  to view this content.