Another Leading Zero Question

  • Thread starter Thread starter Erika
  • Start date Start date
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
does start with 3 digits.

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
 
Back
Top