E

#### Erika

I have another product list that I imported. The product codes are a
combination of numbers and letters. Is there formatting or a formula that
can recognize if the code does not begin with 3 digits to add zeros until it

For Example

06AR - need one leading zero
711BD - needs no leading zero
8MD - needs 2 leading zeros

Hi,

Very similar to the last question.

=REPT("O",3-LEN(LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(INDIRECT("1:50")),1))))))&A1

Mike

I thought the formula might be similar but couldn't get it to work. Thanks
again for you assistance you are saving me so much time. Really appreciate
it!

Maybe simpler

=REPT("O",3-LEN(LOOKUP(10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))))&A1

Mike

Hi,

From your question it is not clear if all entries are structured the same -
that is you always want them to be 5 digits because the should start with 3
numbers followed by 2 letters. If that is the case then use this formula

=RIGHT("000"&A1,5)

Hi Mike,
Cute solution. For the life of me, I can't see why this works ...
Why raise 10^23 ?

Regards,
Bony

Hi,

10^23 is the lookup value and it's such a large number I'm pretty sure it
won't be found at the start of the string. With that starter you should be
able to workout how the formula works.

Mike

Hi Mike,
Excellent. Many thanks!

Have a great weekend!

Regards,
Bony