format numbers

Now format numbers like $1,234.00 in Power Automate

How many times have you tried to format numbers in Power Automate. When you have a number and you would like to convert this to a currency formatted like $1,234.00

I remember trying to format numbers into a currency format a while back Create currency output, but I was never happy with this solution.

Now there is a Format numbers action available in Power Automate the reformatting of number will become so much easier.

Format numbers action

In the Number Functions connector you can now find the Format number action. The Number functions connector is a Built-in connector therefor it will come for free without the need for a premium licence.

Format numbers action

This format number action will sort out a lot of the general number formatting problems that I have faced over the year. Especially the currency formatting used to be a real pain and I never got that 100% right yet.

Format numbers in Power Automate

The format numbers action gives you the following 3 formats as standard

  • 1,234.00
  • -1,234.00
  • $1,234.00

The first option gives you the additional .00 at the end of your number. (Note that the example given is slightly wrong). The second option will add the commas in longer numbers. The 3rd option is the exciting one, currencies.

Okay, the last one definitely make sense, but what are the first and second option about?

And then we still have the locale settings as well.  Time to investigate! Locale setting in format numbers

Locale setting to format currency

I’m going to set the locale to English (United Kingdom)(en-GB) and I’m going to select the format currency option. This now gives me the expected output!

Currency GB

Note the Format being set to C2.

Then I used the first option in the format option and I noticed that this gave me the #.00 format. This will explain why I’m not seeing the comma separating  the thousands.

Locale GB

This is where the second option comes in. The n2 gives me the properly formatted text fro a number.

Format number with commas

Ok, this looks better.

So this now means that we have 3 ways to format the numbers that should cover most of the number formatting issues that I’ve seen.

Rounding up or down currency

Another problem that is important to solve is rounding up or down when we use currencies.

In the below example I’m feeding the format numbers action with 12345.789

rounding currencies

and the format numbers action is clever enough to round up the pennies and give me the result of 12345.79.

Wow, this is really good! But what do all those formats means?

Number formatting strings

Microsoft have documented the number formatting strings, but is still isn’t easy.

First the letters that we can use followed by a number that specifies the number of decimals requried!

C – Currency

D – Decimal

E – Exponenetial

F – Fixed point

G – General

P – Percent

R – Round trip

X – Hexadecimal

Ok, that means we can use this to convert to hexadecimal as well! Just select the Enter custom value and we can make things work! This one single action is getting better and better. I can hardly imagine that you would need anything else to format numbers in Power Automate.

Format numbers hexadecimal

The only tricky thing is now to get the number after the letter right. For example the hexadecimal format would be X0 as the hexadecimal number doesn’t want decimals. If you use X as a format string then you will get the following error:

Unable to process template language expressions for action ‘Format_number’ at line ‘1’ and column ‘2794’: ‘The template language function ‘formatNumber’ format string ‘X’ is not valid. The format string should be a numeric format string. Please see https://aka.ms/logicexpressions#FormatNumber for usage details.’.

The formatNumber Function

If you want to avoid using the format number action then there is also a formatNumber function that you could for example use in a Compose action or a Select action.

The user voice

The uservoice related to this issue is still there showing how many people need this solutions. Thank you Microsoft! This is a great new edition!

2 thoughts on “Now format numbers like $1,234.00 in Power Automate

  1. thats great news. Is this workable within a HTML table? i’m struggling to format currency numbers in that. thanks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: