Remove trailing part of text and adding zeros


i have a text column in which in import PIN number. Examples SA03.5566 or SA03.234 or SA03.12

I want to convert the PIN into 9 digits without SA03. and add zeros to the left automatically using a formula

Example 000005566
Example 000000234
Example 000000012

Can any one create a formula for me please?


Here an example if SA03. will always be the same string.

left("000000000", 9 - len(substitute({PIN}, 'SA03.', ''))) & substitute({PIN}, 'SA03.', '')



Worked like a charm.

Thank you

1 Like

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.