Padding Zeros to Your Field/Column

[JoeM]

You might be looking to pad your values with some zeros, but can't quite find an easy way to do so. Here is a quick way to achieve what you might want. In this particular example, we will be padding the left side of account numbers to lead with zeros.

Here we have an 'Account' field where the values are any length. I am looking to pad any of these numbers with leading zeros as long as the length is 7 characters long.

Note: By left padding numbers, you will be changing what might be perceived as a numerical value to a string value. By doing so, you forfeit functionality reserved for numeric analysis (like filtering types, etc.),

Account

2123

33403

309432

20202

12

1232455

In this 'Account.' field, I am working with a numeric field, so I'm going to write a formula that also includes a type conversion to string.

Formula (Pad Left):

substring(
	"0000000", 
	length(
		string(
			Account
		)
	)
) + string(Account)

Formula (Pad Right):

string(
	Account
) + substring(
	"0000000", 
	length(
		string(
			Account
		)
	)
)

To update these formulas, write in the max padding string (in this case 7 zeros to pad up to 7 characters).
Result:

Account	Pad Left	Pad Right
12	0000012	1200000
2123	0002123	2123000
20202	0020202	2020200
33403	0033403	3340300
309432	0309432	3094320
1232455	1232455	1232455