Formula

[Nikki]

Hi All

I am wanting to use one formula to keep the alphabetical character but add leading zeros if the digits are less than 8.

For example A1234 must be converted to A0001234.

Many thanks, Nikki

 

[Nebu]

Hi:

Try the following formula

=IF(LEN(A1)-1<8,LEFT(A1,1)&REPT("0",8-(LEN(A1)-1))&RIGHT(A1,LEN(A1)-1))

Thanks

 

[Nikki]

Hi All

I am wanting to use one formula to keep the alphabetical character but add leading zeros if the digits are less than 8.

For example A1234 must be converted to A0001234.

Many thanks, Nikki

Hi:

Try the following formula

=IF(LEN(A1)-1<8,LEFT(A1,1)&REPT("0",8-(LEN(A1)-1))&RIGHT(A1,LEN(A1)-1))

Thanks

 

[Nikki]

Thanks so much Nebu for your speedy reply - will look at this a little later.

Much appreciated.

 

[Somendra Misra]

Hi Nikki,

One more solution, but array formula, so must be entered with Ctrl+Shift+Enter.

=LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)&TEXT(MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255),REPT(0,8-LEN(A1)+LEN(MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255))))

Regards,

 

[Roja]

And what if I want result like below:

For example TAM/GBT/H/06587 must be converted to 8 digit GH006587.

 

[Nikki]

Thanks Somendra - will try wrap my head aroun this a little later! Much appreciated

Hi Nikki,

One more solution, but array formula, so must be entered with Ctrl+Shift+Enter.

=LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)&TEXT(MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255),REPT(0,8-LEN(A1)+LEN(MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255))))

Regards,

-

 

[Somendra Misra]

And what if I want result like below:

For example TAM/GBT/H/06587 must be converted to 8 digit GH006587.

Can you give some more examples of data?

Regards,

 

[John Jairo V]

Hi to all!

One more solution (without CSE):
=LEFT(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1/17))-1)&TEXT(MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1/17)),99),REPT(0,9-MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1/17))))

Blessings!

 

[Roja]

Hi Somendra,

I have enclosed a file for your reference.

 

[Nebu]

Hi:

If your all IDs are going to be consistent with the format given in the uploaded file it is pretty straight forward, use the following formula.

=MID(A2,5,1)&MID(A2,9,1)&"0"&RIGHT(A2,5)

Thanks

 

[Roja]

Thank you all for your support

 

[Deepak]

Thank you all for your support

@ Roja
Next Time!! Pls start a new thread for your very own question as well as don't forget to hit like if the contributor efforts helped.