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%?
Page 1 / 1
Use the ROUND() function to round the final value to two decimal places before adding the “%” to the end.
ROUND(Value, 2) & "%"
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.
Thanks, Justin!
How/where do I put it in this formula?
CONCATENATE({Infusion vs Prod},"-(", ({Formula %}*100), “%)”, {Latin Name / Specifics}, " (", {Ingredients / Product Type}, “)”)
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}, ")")
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!
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.