Using a loop to add '0's in between two other numbers to reach 12 digits total

I have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC.

I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT.

What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.

I guess I need a loop of some sort?

Thanks in advance.

Assuming your short UPC is in cell A1, use this formula in another cell:

Please Login or Register  to view this content.

Worked perfectly, thank you. Just by chance is there any way to combine this with =IF(ISERROR(VALUE($B3)),$A3,$B3) ?

SOLVED: =CONCATENATE(4,REPT(0,11-LEN(A1)),A1)

Thanks

I don't know what you have in $A3 or $B3, but if I have to guess, I'd say:

Please Login or Register  to view this content.