This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Concatenate percentages so they show as percen...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

1
4702
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 18, 2021 06:14 PM

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

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 18, 2021 09:34 PM

Reply

5 Replies 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 18, 2021 06:46 PM

Use the `ROUND()`

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

```
ROUND(Value, 2) & "%"
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 18, 2021 07:36 PM

How/where do I put it in this formula?

CONCATENATE({Infusion vs Prod},"-(", ({Formula %}*100), “%)”, {Latin Name / Specifics}, " (", {Ingredients / Product Type}, “)”)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 18, 2021 08:39 PM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 18, 2021 09:25 PM

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}, ")")
```

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 18, 2021 09:34 PM

Reply