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
- Show & Tell
- Re: Formatting numbers using regular expressions

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

0
4623
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

Sep 06, 2021 11:42 AM

Every so often someone asks how to format numbers in a string the same way that they appear in a number or currency field. Various formulas have been created in the past (including some that I’ve posted in threads on this topic), but a recent thread prompted me to take another stab at the concept, and I came up with the most condensed formula that I know of for formatting **whole** numbers.

The formula below will currently handle numbers—positive or negative—up to 21 digits long (<= 999 quintillion), though with a single change it could support higher values if needed.

```
IF(Number < 0, "-") &
REGEX_REPLACE(
ABS(Number) & "",
REPT("(\\d{3})", (ROUND((LEN(ABS(Number) & "") + 1) / 3)) - 1) & "$",
LEFT(",$1,$2,$3,$4,$5,$6", 3 * ((ROUND((LEN(ABS(Number) & "") + 1) / 3)) - 1))
)
```

If you want this displayed as currency, change the first line of the above formula to this:

```
IF(Number < 0, "-") & "$" &
```

To change the separators to periods for some other regions, replace the commas in the string that’s passed to the `LEFT()`

function in the next-to-last line of the formula; e.g.:

```
LEFT(".$1.$2.$3.$4.$5.$6", ...
```

To support even larger numbers, extend that string using the same pattern. Each additional `[separator]$[number]`

combo will add support for three more digits.

```
LEFT(",$1,$2,$3,$4,$5,$6,$7,$8", ...
```

I may revisit this later on to support numbers with decimals, but it’s tough to know how far down the road that might be.

Reply

7 Replies 7

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

Sep 06, 2021 04:29 PM

@Justin_Barrett If you ever decide to purchase a license to Ready Made Formulas you will see that I also use regular expressions in my formula for formatting numbers as strings. (On the other hand, if you want to puzzle this one out on your own, you may not want to look at my formula ;-).

I found that the upper limit on number size for my formula was due to Airtable returning very large numbers in scientific notation. I don’t remember the exact number of decimal places where scientific notation kicked in, but I included some logic to deal with that without having to know the exact number in case Airtable changes it. (I also have an option to exclude that logic for people who know that there numbers will never get that big and want a slightly simpler formula.)

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

Sep 08, 2021 12:30 AM

Thank you, the Euro version would be :slightly_smiling_face:

```
IF(TODO_REPLACE_WITH_YOUR_COLUMN < 0, "-") &
REGEX_REPLACE(
ABS(TODO_REPLACE_WITH_YOUR_COLUMN) & "",
REPT("(\\d{3})", (ROUND((LEN(ABS(TODO_REPLACE_WITH_YOUR_COLUMN) & "") + 1) / 3)) - 1) & "$",
LEFT(" $1 $2 $3 $4 $5 $6", 3 * ((ROUND((LEN(ABS(TODO_REPLACE_WITH_YOUR_COLUMN) & "") + 1) / 3)) - 1))
)
& '€'
```

I’ve added this thread as resource for https://github.com/UnlyEd/airtable-utils :slightly_smiling_face:

Reply

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

Sep 10, 2021 11:57 AM

Spending way too much time on something no one will ever notice or likely even see?

Lovely, here’s the premise of another formatter following a similar train of thought, just a bit less refined:

```
SWITCH(
LEN(
"" & yourFieldName),
1, " " & yourFieldName,
2, " " & yourFieldName,
3, yourFieldName)
```

whenever dealing with sub-mille data that varies a lot, triggering my OCD.

Reply

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

Feb 27, 2023 10:41 AM

Any chance of seeing an update to this that includes decimal support? It would be very helpful.

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

Mar 13, 2023 11:36 PM

Super helpful, thank you.

How about a version that supports rounding and abbreviation like 10K, 10M, 3.4B, etc.? 🙏

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

May 19, 2023 04:59 PM - edited May 20, 2023 08:50 AM

I took this example from the OP which seemed to be limited to just whole numbers and manipulated it a bit to take into account the decimal places as well. Seems to be working. The target is a currency field (which is obviously past due and the left column in the pic below) . I just sort of hacked at this until I figured something out but it is much smaller than some of the others I have found. Assumes 2 decimal places (i.e. currency). I guess if you only want 1 decimal, you could round the whole thing with 1 decimal. Probably would work. Basically if you got whole numbers and 2 decimal numbers, this will format it to a currency number with 2 decimals and a $ sign plus.a space in front of it as a text string. I use this to fill in dollar amounts in PDFs and even though Airtable shows it as formatted, the raw data being sent to the PDF Filler is just a regular number. I thought maybe using formatting in the PDF to say that this field is a currency would resolve it, but it did not. I am not a programer BTW.

```
IF(SEARCH(".",CONCATENATE({Past Due Amount})),IF({Past Due Amount} < 0, "-") & "$ " &
REGEX_REPLACE(
ABS(LEFT(CONCATENATE({Past Due Amount}),LEN(CONCATENATE({Past Due Amount}))-3)) & "",
REPT("(\\d{3})", (ROUND((LEN(ABS(LEFT(CONCATENATE({Past Due Amount}),LEN(CONCATENATE({Past Due Amount}))-3)) & "") + 1) / 3)) - 1) & "$",
LEFT(",$1,$2,$3,$4,$5,$6", 3 * ((ROUND((LEN(ABS(LEFT(CONCATENATE({Past Due Amount}),LEN(CONCATENATE({Past Due Amount}))-3)) & "") + 1) / 3)) - 1))
)&MID(CONCATENATE({Past Due Amount}),LEN(CONCATENATE({Past Due Amount}))-2,LEN(CONCATENATE({Past Due Amount}))),(IF({Past Due Amount} < 0, "-") & "$ " &
REGEX_REPLACE(
ABS({Past Due Amount}) & "",
REPT("(\\d{3})", (ROUND((LEN(ABS({Past Due Amount}) & "") + 1) / 3)) - 1) & "$",
LEFT(",$1,$2,$3,$4,$5,$6", 3 * ((ROUND((LEN(ABS({Past Due Amount}) & "") + 1) / 3)) - 1))
))&'.00')
```

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

May 09, 2024 07:48 PM

You are amazing. This is complex formula and i try to learn it.

Thank you so much.