Another Leading Zero Question

[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
does start with 3 digits.

For Example

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

 

[Mike H]

Hi,

Very similar to the last question.

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

Mike

 

[Erika]

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!

 

[Mike H]

Maybe simpler

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


Mike

 

[Shane Devenshire]

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)

 

[Bony Pony]

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

Regards,
Bony

 

[Mike H]

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

 

[Bony Pony]

Hi Mike,
Excellent. Many thanks!

Have a great weekend!

Regards,
Bony