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
- Formular to Extract Numbers out of a string

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

Topic Labels:
Formulas

Solved

Jump to Solution

0
520
5

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

Oct 21, 2024 07:28 AM

Hi Community,

i have a single select field with the following options.

I want to add up the results from different columns in a formula. To do this, I need to extract the score from the rest. For example, only “2” should be extracted from “2 More then 20%” in the formula.

What is the best way to do this?

Thanks & best

Mirko

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

Oct 22, 2024 05:28 AM

JFYI, default second parameter for LEFT() , RIGHT() functions is 1.

So, LEFT({Field}, 1) equals to LEFT({Field})

But it seems like need to re-check you solution, due to other reason.

If you set {Project cost / total Portfolio budget} to '10 Less than 5%', formula will count it as 1 instead of 10.

So, you might want to change all numbers '1' to '2' in your formula. At least, in fields, containing options with 2-digit numbers. Or if such numbers absent now but could be added in future.

I usually perform such 'total replaces' in a following way (just an example)

of course, 7*100 changed to 7*200 must be returned to 100 manually after 'Replace All'

5 Replies 5

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

Oct 21, 2024 09:19 AM - edited Oct 21, 2024 09:44 AM

Hi,

Try simple

`VALUE({Field Name})`

for options, starting with number, must be OK.**Note:** if text contains dash, anywhere, value will be negative (like "5 Between 5 - 20%")

if you have only positive numbers, additionally wrap into ABS

`ABS(VALUE({Field Name}))`

If you need something more complex, use

`REGEX_EXTRACT({Field}, '^\d+ ' )`

means "take one or several digits from the beginning, followed by space". or

`REGEX_EXTRACT({Field},'^\S+ ')`

"take one or several __any characters except space__ from the beginning, followed by space", if you have negative or floating point numbers.

Return value is string so you should wrap it into VALUE( ) to get a number.

Reply

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

Oct 21, 2024 11:26 PM

Hi, thx for the answer.

I found a solution. Here the formular:

Best

Mirko

Solved
See Solution in Thread

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

Oct 22, 2024 05:28 AM

JFYI, default second parameter for LEFT() , RIGHT() functions is 1.

So, LEFT({Field}, 1) equals to LEFT({Field})

But it seems like need to re-check you solution, due to other reason.

If you set {Project cost / total Portfolio budget} to '10 Less than 5%', formula will count it as 1 instead of 10.

So, you might want to change all numbers '1' to '2' in your formula. At least, in fields, containing options with 2-digit numbers. Or if such numbers absent now but could be added in future.

I usually perform such 'total replaces' in a following way (just an example)

of course, 7*100 changed to 7*200 must be returned to 100 manually after 'Replace All'

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

Oct 22, 2024 07:53 AM

Hi, yes a realised this today as well and found the same solution. 🤣

Thanks a lot!

Reply

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

Oct 23, 2024 05:41 PM

Seems like you have a solve, but just throwing out another idea - linked fields.

You could make another table where the name is each of these options and there is a numerical value for each. Link to that table and do a lookup of that value in your first table.