How To Format A Number In Power Automate [Many Examples]

power automate format number

The way numbers need to be displayed varies on the context. Prices with different currencies need a different format than serial numbers with leading zeros. The Power Automate Format Number action and the interchangeably usable Power Automate formatNumber function simplifies formatting numbers.

Within this article you will learn

  • How to use the Power Automate Format Number action
  • How to use the Power Automate formatNumber function in expressions
  • How to formulate format patterns according to your needs (lots of examples)

How To Use The Power Automate Format Number Action

Let’s format a number to the common US format for a dollar price. As an example, we will format 34800.67 to $34,800.67.

  1. Create a new flow with a manual trigger

    Power Automate Format Number New Flow

    New flow

  2. Add a Format Number action

    Power Automate Format Number Action Add

    Power Automate – Format Number action

  3. Configure Format Number action: Number = 34800.67, Format = select $1,234.00 and Locale = select English (United States) (en-US)

    Power Automate Format Number Action

    Power Automate – Format Number action

  4. Run the Format Number action flow

    Power Automate Format Number Action Result

    Power Automate – Format Number action – Result Test Run

power automate format number

Power Automate FormatNumber Function

The formatDate function does the same as the Format Date action. A given number is formatted based on a format and a locale to formatted string.

Syntax

formatNumber(number : number, format: string, locale?: string)

Parameters

  • number (required): The number that needs to be formatted.
  • format (required): The format that should be applied to format the number.
  • locale (optional): The locale that is used to format the number.

Return value

  • Returns the formatted number as a string that is formatted based on format and locale.

Power Automate Format Number Patterns

To format a number you need to provide a format. Formulating this format is for sure the hardest part.

We will show you the options you have for formulating formats and provide a lot of examples.

To define the number format you can use:

  • Standard formats for Currency, Digits, Exponential, Float, Hexadecimal, Number or Percent
  • Custom format

Below you will find explanations and examples of each format.

In most cases the Power Automate Format Number examples below are self-explanatory.

Currency

To define the format of for a currency there is the standard C format. Simply use C followed by the number of decimal places you need.

If you only provide C as format you will get the default format C2.

Provide a locale to formatNumber to get the correct currency and format for the given locale.

Examples: Format number as currency

FormatExpressionNumberResult
CformatNumber(12345,’C’)12345$12,345.00
C0formatNumber(12345,’C0′)12345$12,345
C1formatNumber(12345,’C1′)12345$12,345.0
C2formatNumber(12345,’C2′)12345$12,345.00
C3formatNumber(12345,’C3′)12345$12,345.000
C with german localeformatNumber(12345,’C’,’de-de’)1234512.345,00 €
Format number as currency

Digits

It is quite common to have numbers that need to have certain number of digits. To provide the needed number of digits leading zeros are often used to fulfil the needed length. The standard format D followed by the number of digits you need ensures the rigth number of digits.

Examples: Format number with leading zeros

FormatExpressionNumberResult
DformatNumber(123,’D’)123123
D3formatNumber(123,’D3′)123123
D4formatNumber(123,’D4′)1230123
D6formatNumber(123,’D6′)123000123
D8formatNumber(123,’D8′)12300000123
Format number with leading zeros

Exponential

To format a number in exponential notation use the standard format E.

Examples: Format number in exponential notation

FormatExpressionNumberResult
EformatNumber(12345,’E’)123451.234500E+004
Format number in exponential notation

Float

You want to format a number with predefined number of decimal places, use the standard format F followed by the needed number of decimal places.

Examples: Format number as a float

FormatExpressionNumberResult
FformatNumber(12345,’F’)1234512345.00
F0formatNumber(12345,’F0′)1234512345
F1formatNumber(12345,’F1′)1234512345.0
F2formatNumber(12345,’F2′)1234512345.00
F3formatNumber(12345,’F3′)1234512345.000
Format number as a float

Hexadecimal

To format a number in hexadecimal use the standard format X.

Examples: Format number hex string

FormatExpressionNumberResult
XformatNumber(987,’X’)9873DB
Format number as hex string

Number

To format a number with a thousand separator and a configurable number of decimal places use the standard format N.

Examples: Format number with thousand separator

FormatExpressionNumberResult
NformatNumber(12345,’N’)1234512,345.00
N0formatNumber(12345,’N0′)1234512,345
N1formatNumber(12345,’N1′)1234512,345.0
N2formatNumber(12345,’N2′)1234512,345.00
N3formatNumber(12345,’N3′)1234512,345.000
Format number with thousand separator

Percent

The standard format P lets you display a number as a percentage.

Examples: Format number to percentage

FormatExpressionNumberResult
PformatNumber(0.5,’P’)0.550.00%
P0formatNumber(0.5,’P0′)0.550%
P1formatNumber(0.5,’P1′)0.550.0%
P2formatNumber(0.5,’P2′)0.550.00%
P3formatNumber(0.5,’P3′)0.550.000%
Display percentage with the specified number of decimal places.

Custom Format

Sometimes the standard formats do not fit your needs. This is where custom formats come in.

By combining multiple placeholders you can define your own format.

Here is a list of the most common placeholders.

PlaceholderDescription
0A digit if present, if there is no digit a 0.
#A digit if present, if there is no digit nothing is displayed.
.Use a decimal separator and a fixed number of decimal places.
,Use thousands separator.
%Multiplies the number by 100 and appends a % symbol.
;Define three sections with separate format strings for values greater than 0, less than 0 and equals 0
Placeholder for custom formats

You might wonder how to use them. Below there is at least one example for every placeholder.

0 – Zero Placeholder

FormatExpressionNumberResult
00000000formatNumber(12345,’00000000′)1234500012345
00.00formatNumber(12345,’00.00′)1234512345.00
Custom Format – Zero Placeholder Example

# – Digit Placeholder

FormatExpressionNumberResult
########formatNumber(12345,’########’)1234512345
Custom Format – Digit Placeholder Example

. – Decimal Point Placeholder

FormatExpressionNumberResult
#.00formatNumber(12345,’#.00′)1234512345.00
Custom Format – Decimal Point Placeholder Example

, – Thousands Separator

FormatExpressionNumberResult
#,#formatNumber(12345,’#,#’)1234512,345
Custom Format – Thousands Separator Example

% – Percent

FormatExpressionNumberResult
#00.##%formatNumber(0.1234,’#00.##%’)0.123412.34%
Custom Format – Percent Example

; – Section Separator

FormatExpressionNumberResult
+0.00;-0.00;0formatNumber(1234,’+0.00;-0.00;0′)1234+1234.00
Custom Format – Section Separator Example

Leave a Comment

Your email address will not be published.