maximum charachters in cells

G

Guest

I try to write a number with 30 figures (number), like
123,456,789,123,456,789,123,456,789,012.
In the cell the result was
123,456,789,123,456,000,000,000,000,000.
My problem is that I want to divide my number to 97 but
the result is modified by Excel taking in to account the
result with many zeros.
I need to make this operation for IBAN codes (new banks
account number in Europe) but the Excel do not allow me.
If anybody can explain me this error, please answer me to
e-mail : [email protected]
 
F

Frank Kabel

Hi
this is not an error but a documented limitation: Excel
supports only 15 significant digits. You have exceeded
this maximum

I doub't that you want to calculate with these IBAN numbes
so you may try:
- format the cell as 'Text' BEFORE entering a value
- preceede the entry with an apostrophe. e.g.
'1234567890123445567899
 
P

Peter Atherton

I can't answer this correctly yet. But Excel cannot
process numbers with more than 15 numbers. So enter the
figure as text, i.e. place an apostrophe before the number.

Test formula with 100 as divider data
123,456,789,123,456,789,123,456,789,012 entered in B18
Click to expand...

formula:
=ROUNDDOWN(MID(B18,2,19)/100,0)&ROUNDDOWN(RIGHT
(B18,19)/100,0)

results in 12345678912347891234567890

Perhaps someone can tweak this

Regards
Peter
 
P

Peter Atherton

More thoughts.

You need to enter ID as text and use three helper columns
(I used rows)
ID >123,456,789,123,456,789,123,456,789,012
left ID/97 1272750403334.60000
ID right/97 8135293368958.890
Middle number 59.790
Result 1272750403334598135293368958

formula
leftID =MID(B26,2,19)/97
Right ID/97 =RIGHT($B$26,19)/97
Middle =MOD(B27,INT(B27))*100
result =""&INT(B27)&INT(B29)&INT(B28)

regards
Peter
 
Top