specify data size in cell add 0's

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

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"donna13pa" wrote:

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

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

"donna13pa" wrote:

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