cancel
Showing results for 
Search instead for 
Did you mean: 
JoeM
Community Manager
Community Manager
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
Contributors
Version history
Last update:
‎04-26-2022 09:21 AM
Updated by: