Change a 1,2,3 or 4 digit number to a 6 character text string

[Steve D]

Hi, I have a file that has a column with either 1,2,3 or 4 digit numbers. I
need to convert that value to a 4 digit number and add '02 to the front of
it. For example if A1 = 34 I would like B1 to be equal to '020034, if A1 =
134 then B1 should be '020134. Here is the formula I tried to use but it
keeps converting the number into a text string. How can I make this work?

=IF(FIXED(LEFT(A1,4),0)<10,"02000" &
LEFT(A1,1),IF(FIXED(LEFT(A1,4),0)<100,"0200" &
LEFT(A1,2),IF(FIXED(LEFT(A1,4),0)<1000,"020" & LEFT(A1,3),"02" & LEFT(A1,4))))

 

[PCLIVE]

One way,

="02"&REPT(0,6-(LEN(A1)+2))&A1

Note, this will remain as text and not a number. This is necessary to
maintain the zero at the beginning. Otherwise, you'd have to change the
cell formatting.

=INT(2 & REPT(0,6-(LEN(A1)+2))&A1)

Change cell format to 000000

HTH,
Paul

 

[Tom Hutchins]

If your column has actual numbers, you can simplify your formula:
=IF(A1<10,"02000" & A1,IF(A1<100,"0200" &A1,IF(A1<1000,"020" & A1,"02" & A1)))

This gives the desired output as text, because you are concatenating the
text "02" with the number in column A. If you want an actual number in column
B as your output, but that shows the inital zero, you can do it this way:
- Your formula in B1 would be =20000+A1
- Copy B1 down as needed
- Select the cells in column B and format them to show the inital zero.
Select Format >> Cells >> Custom. In the Type box, enter 000000

Hope this helps,

Hutch

 

[Pete_UK]

One way:

="02"&TEXT(A1,"0000")

Copy down as required.

Hope this helps.

Pete