specify data size in cell add 0's

  • Thread starter Thread starter donna13pa
  • Start date Start date
D

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
 
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
 
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
 
Back
Top