Do you want to add leading zeros to your numbers in Power Automate?
A common task with data is adding leading zeros to numbers. Leading zeros can be essential for maintaining the proper format of identification numbers, invoice numbers, or other unique identifiers that require a specific number of digits.
This article will discuss the utilization of the formatNumber function, Format number action, and other string functions to achieve padding text or numbers with leading zeros.
- Power Automate offers multiple methods to add leading zeros to numbers.
- The formatNumber function and Format number action can be used to format numbers with leading zeros.
- A combination of substring, length, and concat functions can be used for adding leading zeros to text data.
What are Leading Zeros?
Leading zeros are the digits “0” added before the first non-zero digit in a number. They serve to maintain a fixed width for numerical values, which is especially useful in sorting, aligning, or comparing data.
While working with numbers, you might come across situations where you need to pad them with zeros. For instance, when dealing with unique identification numbers or codes, you may need to ensure a fixed number of digits for consistency.
Add Leading Zeros to Numbers with the Format Numbers Action
In Power Automate, you can easily add leading zeros to your numbers using the Format Numbers action. To get started, follow these simple steps.
- First, add a new action in your flow by selecting New action under the desired step. In the search bar, type Format and choose the Number format action from the list. Now, you will need to configure the action’s properties.
- Add the number or dynamic reference to the number you want to pad with zeros in the Number field.
- Select the Enter custom value option from the dropdown list in the Format field. Now, enter a string of zeros such as
000000in the Format field. This string of zeros should have the total number of characters that you want the number to contain after padding with starting zeros. In this example, entering
000000will convert the number
- Set the Locate to Invariant Language (Invariant Country) (). This is equivalent to not selecting a locale.
When your flow runs, you will get numbers padded with zeros. This is a very user friendly way to add zeros to the start of your numbers.
Add Leading Zeros to Numbers with formatNumber Function
The formatNumber function can also be used for adding leading zeros to numbers.
The function can be placed directly in any field where you want to add your number with leading zeros.
- Click into the field where you want to add the leading zeros. This will open the Expression popup where you can enter the required expression.
- Select the Expression tab from the popup.
- Enter the above expression into the formula editor where
outputs('Format_number')?['body']is the reference to the number you want to add zeros.
- Press the OK button.
The formatNumber function has a 3rd argument for the locale to be used, but this can be left empty.
First, you’ll want to create a new flow action that includes a Compose action. This will contain the expression that adds leading zeros to your number. Within the Compose action, enter the following expression:
formatNumber(yourNumber, '00000'). Replace
yourNumber with the number you want to add leading zeros to, while keeping the format as is.
formatNumber function operates by applying the specified formatting pattern. In this case, the pattern ‘00000’ ensures the output will always show five digits, adding leading zeros when necessary. For instance, if your original number is 22, the output will be 00022.
You can customize the pattern for your specific needs. For example, if you require a six-digit output, adjust the pattern to
💡 Tip: Remember to include the single quotation marks
' around the pattern when entering the expression.
Add Leading Zeros to Text with the substring Function
The first two methods will work great when trying to add leading zeros to numbers, but you will get errors when trying to pad text.
To achieve this for text strings, you will need to use a more complex formula expression.
substring( concat( '00000000', outputs('Compose') ), sub( add( 8, length(outputs('Compose')) ), 6 ), 6 )
The expression is using the substring function to extract a portion of the string after concatenating the string with leading zeros. The trick is to figure out the correct number of characters to return with the substring function.
concat('00000000', outputs('Compose')): This part of the expression concatenates the string 00000000 with the output of a previous step in the workflow, accessed through
outputs('Compose'). This is done to ensure that the output string has at least 8 zero characters at the start.
length(outputs('Compose')): This part of the expression calculates the length of the original string string.
add(8, length(outputs('Compose'))): This part of the expression adds 8 to the length of the original string to get the new length after concatenation.
sub(add(8, length(outputs('Compose'))), 6): This part of the expression subtracts 6 from the result of step 3. This is done to calculate the starting index of the substring of interest. In this example, a total character count of 6 is wanted after you include leading zeros.
substring( <step 1>, <step 4>, 6): This is the final expression that extracts a substring of length 6 from the concatenated string generated in step 1, starting from the index calculated in step 4.
This expression pads the output string with zeros to ensure it has at least 8 characters, then extracts a substring of length 6 starting from the end of the string.
💡 Tip: Formula can be used to pad your text with any leading characters. For example, replacing
'AAAAAAAA' with add leading A’s to your text.
You have now learned how to add leading zeros to numbers and text in Power Automate.
This is useful for situations that require a uniform length of string data such as serial numbers or other unique identifiers.
Using the formatting options it is quite easy to add leading zeros to any number. When you require leading zeros with text strings, the task is more difficult but still achievable with the substring, length, and concat functions.
Do you have any other tips for padding numbers or text data in your flows? Let me know in the comments!