Strange situation with filling series of numbers stored as text

[hasanlianar]

Hi,

I have recently encountered with a strange situation in excel and dont know reason for that. To see what is strange please follow instructions below:

- open a new excel file
- convert all cells' format to text
- write any number in cell A1 with less than 11 charachters (for example, 435)
- Fill down, as you will see numbers will be filled in ascending order
- Now in cell B1 write any number with more than 10 charachters (for example, 474647847446)
- Fill down again, as you notice numbers will not be filled in ascending order, the same numbers will be filled.

So, what is the reason?

 

[pgc01]

Hi

I'd say excel uses a 32 unsigned integer value to fill the series. This means that the maximum value possible will be the 10 digit value 4294967295 (2^32-1).

Any value bigger is not converted and simply copied. Try for ex. for 4294967296

Notice that the values will wrap, you'll have 4294967294, 4294967295, 0, 1.

 

[hasanlianar]

Thank you, for explanation

 

[pgc01]

You're welcome

Remark: As I'm sure you've noticed, there's a word missing in my post.

"... excel uses a 32 bit unsigned integer ..."

 

[hasanlianar]

Yes, I noticed.

That was a question in my mind, but since I understood your explanation, I thought it was a minor issue to ask.

Now, I just wonder where do you know this kind of facts about Excel?

Best Regards