Padding Zeros in the middle of a string

[DShack]

Hello All,

I am trying to pad zeros to the middle of this string where the 122 is at. I have tried several different things for example this formula =LEFT(A2,11)&TEXT(MID(A2,12,LEN(A2)),"00000") but nothing seems to be working. I have an excel document that is over 300K rows so a formula would be great to fix this issue. Additionally, the middle record (122 currently) is either 2 or 3 characters and I need it to be 5 characters in length.
Please i need some help! Thank you Everyone!

1301-08020-122-341773-0000-000

 

[Fluff]

Will the number always start at the 12th character?

 

[DShack]

Yes, it will always start in the 12th character.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B
1
2	1301-08020-122-341773-0000-000	1301-08020-00122-341773-0000-000
3	1301-08020-12-341773-0000-001	1301-08020-00012-341773-0000-001
4	1301-08020-1-341773-0000-002	1301-08020-00001-341773-0000-002
Master

Cell Formulas
Range	Formula
B2:B4	B2	=REPLACE(A2,12,FIND("-",MID(A2,12,100))-1,TEXT(MID(A2,12,FIND("-",MID(A2,12,100))-1),"00000"))

 

[Fluff]

Or a slightly simpler formula

=REPLACE(A2,12,0,REPT(0,6-FIND("-",MID(A2,12,100))))

 

[DShack]

Thank you Fluff, both of these work excellent! I should have thought of the replace formula.

 

[Fluff]

You're welcome & thanks for the feedback.