=LEN returns incorrect answer

[MikeF]

In the following table, the LEN formula returns the incorrect number of
characters.

The first number 34,... is in cell b12, it's corresponding LEN formula to
the right in cell b13.
It is the only one that returns correctly, the rest of the table is
obviously inaccurate.

*** The numbers themselves are based on a "to the nth power" formula.

Does anyone know why this is happening?!?
Thanx......

34,596,000,000 11 =LEN(B12)
124,545,600,000,000 15 =LEN(B13)
448,364,160,000,000,000 18 =LEN(B14)
258,257,756,160,000,000,000 16 =LEN(B15)
12,654,630,051,840,000,000,000 18 =LEN(B16)
34,218,119,660,175,400,000,000,000 20 =LEN(B17)

 

[Rick Rothstein]

First off, why do you say only the first number returns the correct length?
It looks to me that the first 3 return the correct length. As for the last
3, that does look odd. Just out of curiosity, how are those cells formatted
(and if they are Custom Formatted, show it to us)?

 

[MikeF]

Rick,

There is no custom formatting, they're merely "Number", no decimal
places/show thousand separator.

And yes, in this example the first three are correct.

Nonetheless, this is serious [at least for me!].

Is it possible the LEN formula stalls after 18 or 19 characters?

By now, presume someone has copied the table - or built a facsimile - and
rcvd the same results.

Any new thoughts?

Thanx again.
- Mike

 

[Rick Rothstein]

Okay, I see the problem. The number you are seeing in the cell is not the
value for the cell... it is the number (pared down to 15 significant digits
maximum... all Excel will hold for you) shown to you with a Cell Format of
Number with thousands separator. However, take a look at the value shown in
the Formula Bar for one of those "funny" cells... it is not the number you
type in; rather, it is a number in E-Notation. For example, the cell with
this...

34,218,119,660,175,400,000,000,000

in it actually has a value of this...

3.42181196601754E+25

and *that* value is 20 characters long. This is no different than if you had
typed 123 in a cell with a Custom Format of this...

0"-"0"-"0

the cell would display 1-2-3, but the value in the cell is 123 and if you
used the LEN function on the cell, it would report 3 (the number of digits
in the actual value in the cell).

Back to your original problem.... if you format the cell as Text, or enter
your number with an apostrophe in front of it to make it a text entry, then
the LEN function will return the length you are expecting.

--
Rick (MVP - Excel)

Rick,

There is no custom formatting, they're merely "Number", no decimal
places/show thousand separator.

And yes, in this example the first three are correct.

Nonetheless, this is serious [at least for me!].

Is it possible the LEN formula stalls after 18 or 19 characters?

By now, presume someone has copied the table - or built a facsimile - and
rcvd the same results.

Any new thoughts?

Thanx again.
- Mike

First off, why do you say only the first number returns the correct
length?
It looks to me that the first 3 return the correct length. As for the
last
3, that does look odd. Just out of curiosity, how are those cells
formatted
(and if they are Custom Formatted, show it to us)?

 

[MikeF]

Thanx Rick.

I managed to figure it out.

Not the actual "3.42181196601754E+25 notation", which clarifies the reason
for the problem.

But did change the =LEN(b12) to =LEN(TEXT(b12,0)), which did the trick.

- Mike

Okay, I see the problem. The number you are seeing in the cell is not the
value for the cell... it is the number (pared down to 15 significant digits
maximum... all Excel will hold for you) shown to you with a Cell Format of
Number with thousands separator. However, take a look at the value shown in
the Formula Bar for one of those "funny" cells... it is not the number you
type in; rather, it is a number in E-Notation. For example, the cell with
this...

34,218,119,660,175,400,000,000,000

in it actually has a value of this...

3.42181196601754E+25

and *that* value is 20 characters long. This is no different than if you had
typed 123 in a cell with a Custom Format of this...

0"-"0"-"0

the cell would display 1-2-3, but the value in the cell is 123 and if you
used the LEN function on the cell, it would report 3 (the number of digits
in the actual value in the cell).

Back to your original problem.... if you format the cell as Text, or enter
your number with an apostrophe in front of it to make it a text entry, then
the LEN function will return the length you are expecting.

--
Rick (MVP - Excel)

Rick,

There is no custom formatting, they're merely "Number", no decimal
places/show thousand separator.

And yes, in this example the first three are correct.

Nonetheless, this is serious [at least for me!].

Is it possible the LEN formula stalls after 18 or 19 characters?

By now, presume someone has copied the table - or built a facsimile - and
rcvd the same results.

Any new thoughts?

Thanx again.
- Mike

First off, why do you say only the first number returns the correct
length?
It looks to me that the first 3 return the correct length. As for the
last
3, that does look odd. Just out of curiosity, how are those cells
formatted
(and if they are Custom Formatted, show it to us)?

--
Rick (MVP - Excel)



In the following table, the LEN formula returns the incorrect number of
characters.

The first number 34,... is in cell b12, it's corresponding LEN formula
to
the right in cell b13.
It is the only one that returns correctly, the rest of the table is
obviously inaccurate.

*** The numbers themselves are based on a "to the nth power" formula.

Does anyone know why this is happening?!?
Thanx......

34,596,000,000 11 =LEN(B12)
124,545,600,000,000 15 =LEN(B13)
448,364,160,000,000,000 18 =LEN(B14)
258,257,756,160,000,000,000 16 =LEN(B15)
12,654,630,051,840,000,000,000 18 =LEN(B16)
34,218,119,660,175,400,000,000,000 20 =LEN(B17)