Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Concatenate percentages so they show as percentages with 2 decimal points

Topic Labels: Formulas
Solved
Jump to Solution
1024 5
cancel
Showing results for 
Search instead for 
Did you mean: 

I am trying to concatenate two columns, a percentage (based on a formula) in one column and its associated product in the other column. When I concatenate, the percent is converted to a decimal. I know to multiply this result by 100 and add ‘%’ to get the result I want. And it works for most values, but some of the decimals turn out like this: 15.920000000000002%. How do I get all resulting decimals to show as XX.XX%?

1 Solution

Accepted Solutions

Thank you so very much, Justin! This has haunted me for a long time now and will make my life and the work of my teams much easier! It worked perfectly!

See Solution in Thread

5 Replies 5

Use the ROUND() function to round the final value to two decimal places before adding the “%” to the end.

ROUND(Value, 2) & "%"

Thanks, Justin!
How/where do I put it in this formula?
CONCATENATE({Infusion vs Prod},"-(", ({Formula %}*100), “%)”, {Latin Name / Specifics}, " (", {Ingredients / Product Type}, “)”)

If you always want exactly two decimal places, you will need to do more than round. If the rounding results in a number with trailing zeros (e.g. 5.50% or 12.00%) the trailing zeros will not appear with only rounding. If you really want those trailing zeros, my app Ready Made Formulas will build that formula for you, if you have a premium license.

If you are not sure how to combine one formula in with another formula, start with two formula fields, so that the last formula has the result you want. The copy and paste the first formula into the second formula, exactly replacing the name of the first field.

Forcing two digits after the decimal can be done with the help of a regular expression. Because the rounded value might not actually need a decimal point in all cases—e.g. 15.0 translates into just “15” as a string—it gets a little long:

REGEX_EXTRACT(ROUND({Formula %}*100, 2) & IF(NOT(FIND(".", ROUND({Formula %}*100, 2) & "")), ".") & "00", ".*\\.\\d{2}")

Here’s how to insert that into your formula:

CONCATENATE({Infusion vs Prod},"-(", REGEX_EXTRACT(ROUND({Formula %}*100, 2) & IF(NOT(FIND(".", ROUND({Formula %}*100, 2) & "")), ".") & "00", ".*\\.\\d{2}"), "%)", {Latin Name / Specifics}, " (", {Ingredients / Product Type}, ")")

Thank you so very much, Justin! This has haunted me for a long time now and will make my life and the work of my teams much easier! It worked perfectly!