cancel
Showing results for
Did you mean:
Responsive Resident

## Expression to convert decimals into currency strings

EDIT: In my own testing I have discovered that the expression below fails on values less than 0.10, including 0. I'll work on that and update this post.

EDIT EDIT: Updated code with a corrected version! Now sets zero and null values to \$0.00 and values less than 1 to \$0.whatever

Not a question, just wanted to share a useful accomplishment.

So Sharepoint users have dollar(), but us Dynamics/CDS users don't. So here is an expression to convert decimals up to two places into a currency string all in one go. It works for values up to 999,999,999.99

First, without line breaks, for easy copy-pasting. Use the below, and find-replace VALUE with your value.

`if(or(equals(VALUE,0),equals(VALUE,null)),'\$0.00',if(greater(length(string(mul(VALUE,100))),8),concat('\$',substring(string(mul(VALUE,100)),0,sub(length(string(mul(VALUE,100))),8)),',',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),8),3),',',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),5),3),'.',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),2),2)),if(greater(length(string(mul(VALUE,100))),5),concat('\$',substring(string(mul(VALUE,100)),0,sub(length(string(mul(VALUE,100))),5)),',',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),5),3),'.',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),2),2)),if(greater(length(string(mul(VALUE,100))),2),concat('\$',substring(string(mul(VALUE,100)),0,sub(length(string(mul(VALUE,100))),2)),'.',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),2),2)),if(greater(length(string(mul(VALUE,100))),1),concat('\$0.',string(mul(VALUE,100))),concat('\$0.0',string(mul(VALUE,100))))))))`

Here it is with line breaks and comments

```if(or(equals(VALUE,0),equals(VALUE,null)), // Set 0 and null values to \$0.00
'\$0.00',

if(greater(length(string(mul(VALUE,100))),8), // Code for values greater than 1 million
concat('\$',substring(string(mul(VALUE,100)),0,sub(length(string(mul(VALUE,100))),8)),',',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),8),3),',',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),5),3),'.',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),2),2)),

if(greater(length(string(mul(VALUE,100))),5), // Code for values greater than 1 thousand
concat('\$',substring(string(mul(VALUE,100)),0,sub(length(string(mul(VALUE,100))),5)),',',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),5),3),'.',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),2),2)),

if(greater(length(string(mul(VALUE,100))),2), // Code for values greater than 0.99
concat('\$',substring(string(mul(VALUE,100)),0,sub(length(string(mul(VALUE,100))),2)),'.',substring(string(mul(VALUE,100)),sub(length(string(mul(VALUE,100))),2),2)),

if(greater(length(string(mul(VALUE,100))),1), // Code for values greater than 0.09
concat('\$0.',string(mul(VALUE,100))),

concat('\$0.0',string(mul(VALUE,100))) // Code for values 0.09 and below

)))))```
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @jzcase ,

Thank you for posting on the Flow Community Forum!

This is a great way to believe that it will help a lot of users in the community.

Thanks for being an active Microsoft Flow Community Member!

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

Hi @jzcase ,

Thank you for posting on the Flow Community Forum!

This is a great way to believe that it will help a lot of users in the community.

Thanks for being an active Microsoft Flow Community Member!

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.