problem with custom number format

T

toedipper

Hello,

Win xp and Excel 2003

I am trying to use a customer number format of 17 characters in length
00000000000000000

I can format the colum ok but whenever I enter the data eg 10000000000000001
it changes the last 1 to a zero

If I enter for example 10000000000000011 it changes the 2 last 1's to a
zero as well.

Any ideas?

Thanks,

td.
 
J

JE McGimpsey

XL only retains 15 digits of precision. Assuming that you don't need to
do math with your customer number (you must be planning to have a _lot_
of customers!), preformat the cell as Text, or enter it with a leading
apostrophe:

'10000000000000001
 
T

toedipper

Thanks. I am trying to fill a mysql database with test data and the 17
digits relate to the ref no that paypal give for a transaction.

Looks like I'll have to type my 1000 odd test numbers in by hand....

td.
 
J

JE McGimpsey

That would be silly.

Put 1 in A1 and fill the series down to A1000.

in B1, enter ="1000000000000" & TEXT(A1,"0000")
 
H

Harald Staff

Hi Td

10 digits in A1, 7 digits in B1. Formula in C1
=A1&B1

HTH. Best wishes Harald
 
D

Dave Peterson

And if you entered the numbers as numbers, not text...

Put this in C1:
=TEXT(A1,"0000000000")&TEXT(B1,"0000000")
or
=TEXT(A1,REPT("0",10))&TEXT(B1,REPT("0",7))
 
H

Harald Staff

Thanks Dave. Didn't think about leading zeroes. The sample does contain a
clue, I see that now... </blush>

Best wishes Harald
 
Top