Large Numbers

If you format the cells involved for numbers, Excel will not change numbers over 15 digits to scientific notation. This was tested with Excel 2000, other versions may not act the same?

You data seggests you are doing boolean math, Excel does not have a boolean format. So, it will strip off all leading "0's" from your numbers and 111111111111111 + 111111111111111 = 222222222222222. But you can with "If" statements, shift data to simulate Boolean data. JSW

you could use =A1$"1" , this formula put the answer on the left hand side of the cell sujesting that it see it as a TEXT item, but you can still do calulations with the numbers,

hope this helps.

zen

I don't think I did a good job explaining what I was trying to do the first time, I will give it one more try.

I need to start with 1111111111111111 in cell A1 and add 1 to it making cell A2 1111111111111112 and cell A3 1111111111111113 and so on, and I need to do it using a formula so multiple users can just auto fill when they want to add more accounts.

The A1&"1" that was suggested just concatenates a 1 to the cell and doesn't increment by 1 turning the 16 digit number to 17 digits and so on.

I'm using Excel 97, and I'm not having a problem with Excel changes to scientific notation as the cells are formatted to a number with 0 decimal places, but with Excel changing 1111111111111111 to 1111111111111110 and it will not increment any further, it looks like a data type limit.

Format Column A as a number with 0 decimal places
Type 1111111111111111 into cell A1
Type =A1+1 into cell A2
Autofill from A2 to A30 and I get
1111111111111110
1111111111111110
1111111111111110
1111111111111110
1111111111111110
1111111111111110
1111111111111120
1111111111111120
1111111111111120
1111111111111120
1111111111111120
1111111111111120
1111111111111120
1111111111111120
1111111111111120
1111111111111120
1111111111111130
1111111111111130
1111111111111130
1111111111111130
1111111111111130
1111111111111130
1111111111111130
1111111111111130
1111111111111130
1111111111111130
1111111111111140
1111111111111140
1111111111111140
1111111111111140

Just wondering if anyone else had run into this.

I had a similar problem. I called MR EXCEL (he was very helpful and did not charge me anything since he did not resolve the problem) and he told me that Excel will only work with 15 significant digits. If you have numbers longer than that you are out of luck. I would suggest that you use two columns and put some of the digits in one column and the remainder in another column. You can right justify the column on the left and left justify the column on the right. It should look like one number. If you need to do calculations with the number good luck Try adding them or even multiply the most significant digits by the number of places you want to move them and then add the numbers together. I have not checked if this will work - good luck

Numbers other than 1's and 0's

If you enter your numbers as Text, then reference the cells with the text numbers; '123456789123456789 in cell A1,
Then in cell A2 reference A1 as =Value(A1) then do your math with that reference, like: =Value(A1)*2 The result is returned in scientific notation in General cell formatting. If you format the result cell as Number, the digits beyond 15 places are returned as "0's" and any reuse is corrupted. I do not know a workaround for this. The problem is not working with a text number larger than 15 places mathematically its displaying the result as a formatted number! JSW

Mid Function

If you set up your sheet to use text input cells. You can use the mid function to redirect the input to a pair of adjacent columns the first formatted Right the other Left, with half the number in the first column and the other half in the other column. If you do not have a border between them and the striped text numbers are placed as Values, you can do your math on each half, for display purposes. JSW If you enter your numbers as Text, then reference the cells with the text numbers; '123456789123456789 in cell A1,