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

"Erika" wrote:

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

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" wrote:

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" wrote:

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

Maybe simpler

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


Mike

"Mike H" wrote:

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" wrote:

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,

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)


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Erika" wrote:

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 Mike,
Cute solution. For the life of me, I can't see why this works ...
Why raise 10^23 ?

Regards,
Bony

"Mike H" wrote:

Maybe simpler

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


Mike

"Mike H" wrote:

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" wrote:

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,

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" wrote:

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

Regards,
Bony

"Mike H" wrote:

Maybe simpler

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


Mike

"Mike H" wrote:

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" wrote:

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 Mike,
Excellent. Many thanks!

Have a great weekend!

Regards,
Bony

"Mike H" wrote:

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" wrote:

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

Regards,
Bony

"Mike H" wrote:

Maybe simpler

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


Mike

"Mike H" wrote:

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" wrote:

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