Adding 0s to a string that varies in size to make 8 characters

[Usamah22]

I have a series of codes which are missing 0s in them. Each code should be 8 characters with 0s in the middle.

AGO1Y should be AGO0001Y

ACI194F should be ACI0194F

The number of characters vary and so the number of 0s needed will vary. However the 0s would need to go after the 3rd character and the total number of characters will be 8.

Any idea how i can do this?

[afv2688 · Accepted solution]

Hello @Usamah22 ,

Use the following formula:

Left([Field1], 3) + PadLeft( Right([Field1], Length([Field1])-3), 5, '0')

Edit: Add a foto

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[RolandSchubert · Accepted solution]

Hi @Usamah22 ,

you could use the formula
Left([Field1], 3) + PadLeft(Substring([Field1],3), 5, '0')

Left([Field1], 3) take the first 3 characters

Substring([Field1], 3) takes the remaining string, PadLeft  adds '0' to the left side up to total length of 5.

Hope this is helpful.

Best,

Roland

[MarqueeCrew]

@Usamah22 ,

This is dynamic and will work for more cases:

Cheers,

Mark

P.S.  Many solutions can be accepted as there isn't just one way to solve a challenge.