# 6 Time-Saving Baserow Formulas

**URL:** <https://community.baserow.io/t/6-time-saving-baserow-formulas/2130>\
**Category:** Tips&Tricks\
**Tags:** baserow, formula\
**Created:** [January 24, 2023, 1:17pm UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130 "2023-01-24T13:17:40Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![olgatrykush](https://community.baserow.io/user_avatar/community.baserow.io/olgatrykush/32/219_2.png) [@olgatrykush](https://community.baserow.io/u/olgatrykush)\
**Post date:** [January 24, 2023, 1:17pm UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/1 "2023-01-24T13:17:41Z")

</div>

Hello folks,

I’ve prepared an overview of 6 easy but time-saving Baserow formulas with examples and tips to help you improve the integrity of your workflow.

1. **Function:** Length
2. **Function:** Concat
3. **Function:** Date diff
4. **Function:** Trim
5. **Function:** Left/right
6. **Function:** Upper/lower

Check out how these functions work in detail 👀 [6 Time-Saving Baserow Formulas // Baserow](https://baserow.io/blog/6-baserow-formulas).

If you have another great formula you use for data management — share it in the comments. Or if you can’t figure out which formula to use to perform a specific function, feel free to ask here in the forum.

---

<div class="post-metadata">

**Author:** ![Jamac](https://community.baserow.io/user_avatar/community.baserow.io/jamac/32/773_2.png) [@Jamac](https://community.baserow.io/u/Jamac)\
**Post date:** [January 24, 2023, 7:25pm UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/2 "2023-01-24T19:25:20Z")

</div>

I’ve got a few formulas I use all the time, I’ll share them here. All of them make use of the `regex_replace` function, which is so incredibly powerful, I love it.

* * *

This one is probably what I use the most:

```auto
regex_replace(join(totext(lookup('LINK FIELD', 'LOOKUP FIELD')), '§') + '§', '§.*', '')

```

Since formulas in Baserow don’t support array-indexing, you can use this as a lookup which extracts the first value from the array. Very useful when you have link fields that will only ever contain a single link. It will convert the value to a string though, so you have to convert it back if it’s supposed to be something else.

* * *

```auto
if(field('NUMBER FIELD') = 0, '', if(contains(totext(field('NUMBER FIELD')), '.'), regex_replace(totext(field('NUMBER FIELD')), '(?<=\d)(?=(\d{3})+\.)', ','), regex_replace(totext(field('NUMBER FIELD')), '(?<=\d)(?=(\d{3})+$)', ',')))

```

This one converts a numeric field into a comma-separated number. If you use commas for decimal marker and periods for digit separation, just swap all instances of `'.'` and `','`, and change the `\.` in the regex to `,`. And while we’re still waiting on currency fields in Baserow, you can add `'$' + ` (or your preferred currency symbol) right before the part that starts with `if(contains(`, to format a number as a currency.

* * *

These two are useful for dealing with imperial length measurements (feet & inches):

```auto
if(field('NUMBER FIELD') < 12, '', totext(trunc(field('NUMBER FIELD') / 12)) + "'") + if((field('NUMBER FIELD') / 12) = trunc(field('NUMBER FIELD') / 12), '', regex_replace(totext(round(field('NUMBER FIELD') - 12 * trunc(field('NUMBER FIELD') / 12), 2)), '\.?0+$', '') + '"')

```

This takes a numeric value in inches and converts it into ft’in" notation.

```auto
if(contains(field('TEXT FIELD'), "'"), tonumber(left(field('TEXT FIELD'), greatest(search(field('TEXT FIELD'), "'") - 1, 0))) * 12, 0) + if(contains(field('TEXT FIELD'), '"'), tonumber(regex_replace(field('TEXT FIELD'), '.*?(\d+)\"', '\1')), 0)

```

And this converts from ft’in" notation to a numeric value in inches.

* * *

Hope these are helpful to someone!

---

<div class="post-metadata">

**Author:** ![olgatrykush](https://community.baserow.io/user_avatar/community.baserow.io/olgatrykush/32/219_2.png) [@olgatrykush](https://community.baserow.io/u/olgatrykush)\
**Post date:** [January 25, 2023, 8:55am UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/3 "2023-01-25T08:55:58Z")

</div>

Hey @Jamac, thanks for sharing these formulas and for providing descriptions of how they work. I am sure your examples will be very useful and helpful to other community members 🙂

---

<div class="post-metadata">

**Author:** ![Peter](https://community.baserow.io/user_avatar/community.baserow.io/peter/32/4551_2.png) [@Peter](https://community.baserow.io/u/Peter)\
**Post date:** [January 25, 2023, 10:42am UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/4 "2023-01-25T10:42:34Z")

</div>

Maybe we can have a Baserow hosted Baserow database with formulas, explanation and samples 😉 I know you can search Discourse but over time it could get harder to collect or find the formulas.

---

<div class="post-metadata">

**Author:** ![olgatrykush](https://community.baserow.io/user_avatar/community.baserow.io/olgatrykush/32/219_2.png) [@olgatrykush](https://community.baserow.io/u/olgatrykush)\
**Post date:** [January 25, 2023, 10:54am UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/5 "2023-01-25T10:54:07Z")

</div>

Hey @Peter that’s a great idea, and I think @nigel had a similar one. I’ll create a table with these formulas asap. I’ll also create a form, so everyone could add their ‘most used/useful’ formulas to that table.

---

<div class="post-metadata">

**Author:** ![Code\_master](https://community.baserow.io/letter_avatar_proxy/v4/letter/c/d26b3c/32.png) [@Code\_master](https://community.baserow.io/u/Code_master)\
**Post date:** [February 4, 2023, 7:31pm UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/6 "2023-02-04T19:31:35Z")

</div>

Thank you for sharing

---

<div class="post-metadata">

**Author:** ![hslee](https://community.baserow.io/user_avatar/community.baserow.io/hslee/32/2521_2.png) [@hslee](https://community.baserow.io/u/hslee)\
**Post date:** [May 14, 2024, 1:37pm UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/7 "2024-05-14T13:37:27Z")

</div>

Can I know how to use this, especially comma seperated number case?  
Just copying the code to the Formula?

---

<div class="post-metadata">

**Author:** ![hslee](https://community.baserow.io/user_avatar/community.baserow.io/hslee/32/2521_2.png) [@hslee](https://community.baserow.io/u/hslee)\
**Post date:** [May 15, 2024, 3:01pm UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/8 "2024-05-15T15:01:11Z")

</div>

I got to know how to use the above code(for Comman Separated Number).  
It(CSN by the code) is a string, so at the bottom, SUM is not activated.

Is there any solution for using SUM at the bottom for CSN?

---

<div class="post-metadata">

**Author:** ![olgatrykush](https://community.baserow.io/user_avatar/community.baserow.io/olgatrykush/32/219_2.png) [@olgatrykush](https://community.baserow.io/u/olgatrykush)\
**Post date:** [May 20, 2024, 8:48am UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/9 "2024-05-20T08:48:49Z")

</div>

Hey @hslee, could you please elaborate a bit more on this?

> [@hslee](#):
>
> Is there any solution for using SUM at the bottom for CSN?

---

<div class="post-metadata">

**Author:** ![hslee](https://community.baserow.io/user_avatar/community.baserow.io/hslee/32/2521_2.png) [@hslee](https://community.baserow.io/u/hslee)\
**Post date:** [May 21, 2024, 11:38pm UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/10 "2024-05-21T23:38:23Z")

</div>

Comma Seperate Number(number like 120,000)  
is string (if I use the code Jamac wrote the above).

So it cannot be summed at the bottom of baserow.

---

<div class="post-metadata">

**Author:** ![olgatrykush](https://community.baserow.io/user_avatar/community.baserow.io/olgatrykush/32/219_2.png) [@olgatrykush](https://community.baserow.io/u/olgatrykush)\
**Post date:** [May 22, 2024, 9:55am UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/11 "2024-05-22T09:55:28Z")

</div>

Hey @hslee, thanks for the explanation. I suspect you’ve added CSNs to the text field type, which means you can’t use the sum function in footer aggregations. A possible workaround is to create a new field and use a formula to convert these values using the tonumber function. Then, you can use footer aggregations. Here’s what I mean:

 ![image](https://community.baserow.io/uploads/default/original/2X/e/eeba40155980fe59c542b30c99dab1c6515984a5.jpeg)

**Formula 1:** `tonumber(regex_replace(field('CSN'), ',', ''))` - to convert your values into numbers  
**Formula 2:** `if(isblank(field('CSN')), 0, tonumber(regex_replace(field('CSN'), ',', '')))` - to convert your values into numbers plus exclude any empty values if they exist

We also have an issue that will introduce advanced number formatting, which will include a thousand separator format: [Advanced number formatting (#1270) · Issues · Baserow / baserow · GitLab](https://gitlab.com/baserow/baserow/-/issues/1270). This should fix your issue.

Hope it help! 🙂

---

<div class="post-metadata">

**Author:** ![hslee](https://community.baserow.io/user_avatar/community.baserow.io/hslee/32/2521_2.png) [@hslee](https://community.baserow.io/u/hslee)\
**Post date:** [June 10, 2024, 5:29am UTC](https://community.baserow.io/t/6-time-saving-baserow-formulas/2130/12 "2024-06-10T05:29:00Z")

</div>

Thank you for your kind answer.
