specify data size in cell add 0's

[donna13pa]

I want to specify that if data I am importing is 5556946581 it will return as
0000005556946580. It will add 0's to the beginning to make the length 16
characters long and also change the last number to 0 if it is a number from
1-9

 

[Luke M]

Something like this:

=TEXT(MID(A2,1,LEN(A2)-1)&0,"0000000000000000")

Note that if you need this to be a number, you can use:
=VALUE(MID(A2,1,LEN(A2)-1)&0)
and just format cells - number tab, custom format of
0000000000000000

 

[Mike H]

Hi,

I'd import your data and leave it as is and convert to the format you want
with a formula. Say you imported to column A, put this in b1 and drag down

=REPT("0",16-LEN(A1))&LEFT(A1,LEN(A1)-1)&"0"

Mike