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.
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.
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 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!
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.
This is where the second option comes in. The n2 gives me the properly formatted text fro a number.
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
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.
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!
thats great news. Is this workable within a HTML table? i’m struggling to format currency numbers in that. thanks
It should work anywhere you like in flows.
My flow errors when it gets to an Approval. It says it can’t select the value from the “body”.
When i first input the new formatted action into my approval form, it shows as ‘outputs/’ but then after I save the flow and go back in, is shows as ‘body/’ and then fails. grrrr
Can you type the expression in the expression editor?
Something like:
Body(‘action_without_spaces’)
what about a different type of float? in my country we use “1,299.03”
i havent been able to find how to work around this ffloatformat in Power Automate
All you have to do is select the local setting relevant to your location. Then the right currency symbol and ,/. will be used.
I would like to input 12.348,55 and get output as 12,349.55. Basically converting EU to US format. The action doesn’t like the input number and throws “Enter a valid number”. How do I deal with this?
Is the input a text or is atvan actual number. You might have to convert it to a number first if it is a text.