Another Leading Zero Question


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
 
Ad

Advertisements

M

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
 
E

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!
 
M

Mike H

Maybe simpler

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


Mike
 
S

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)
 
B

Bony Pony

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

Regards,
Bony
 
Ad

Advertisements

M

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top