Concatenate percentages so they show as percentages with 2 decimal points

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%?

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.

1 Like

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!

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.