How to prepend zeros to text until certain length

Question

I have a column that looks like this '12345' and another column that tells this number's length.
I need to add leading zeros to it until length in another column. Values in length column are either 8 or 6. If number in this column is less than 6 or 8 then add zeros until it becomes that length. I tried this but this isn't working:

=TEXT(H2,"0000000")

Data:

Edit: output

"033328" or "00009971"

Answer 1

Close to your approach

=""""&TEXT(MID(H2,2,LEN(H2)-2),REPT("0",I2))&""""

Answer 2

Here is another approach:

Formula in C2:

=REPLACE(A2,2,0,REPT("0",B2-LEN(A2)+2))

Answer 3

If you really want to just treat the number column as text, you could do:

=CONCATENATE(REPT("0",I2-LEN(H2)),H2)

Oh, I see the quotes are part of your input text... To discard those (hardwired, or you could do the replace thing):

=CONCATENATE(REPT("0",I5+2-LEN(H5)),MID(H5,2,LEN(H5)-2))

If you did want to do the replace I would probably add a hidden column that holds the unquoted text, and probably another that holds the length of that text in order to avoid the repetition in the formula.

Answer 4

Perhaps the following - SUBSTITUTE to replace the quotes, TEXT to add leading zeros, and then RIGHT to size to the appropriate length:

=RIGHT(TEXT(SUBSTITUTE(H2,"""",""),"00000000"),I2)

If you need the quotes in the output, then use the following:

=""""&RIGHT(TEXT(SUBSTITUTE(H2,"""",""),"00000000"),I2)&""""