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

Question

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 Answer

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 Answer

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