Formula to add zeros

[TIFFSTER2001]

I have data that generates in this format T/12345/P0 that need to be turned into this format (T00025089P5), and always has 11 characters. The middle number (12345) is called the group. The group can be anywhere from 1-10000 but zeros need to be added before the group to get to the 11 characters as in this example, T00025089P5. What formula can be used to always create the 11 characters?

 

[Sufiyan97]

Try

Book3
	A	B	C
1	T/12345/P0	T00012345P0
2
3
4
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(A1,"/",REPT(0,11-LEN(SUBSTITUTE(A1,"/",""))),1),"/","")

 

[Fluff]

@TIFFSTER2001 what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[kvsrinivasamurthy]

In B3:

="T/"&TEXT(A3,"00000")&"/PO"

A	B
1​	T/00001/PO
2​	T/00002/PO
240​	T/00240/PO
2560​	T/02560/PO
30000​	T/30000/PO

 

[TIFFSTER2001]

Try

Book3
	A	B	C
1	T/12345/P0	T00012345P0
2
3
4
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(A1,"/",REPT(0,11-LEN(SUBSTITUTE(A1,"/",""))),1),"/","")

That worked! Thank you so much

 

[TIFFSTER2001]

@TIFFSTER2001 what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you, I will do that.

 

[Sufiyan97]

You're welcome.
Glad we could help.