Formular to Extract Numbers out of a string

[mirkolando]

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 

[Alexey_Gusev]

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.

[mirkolando]

Hi, thx for the answer. 

I found a solution. Here the formular: 

Best

Mirko 

[Alexey_Gusev · Accepted solution]

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'

[mirkolando]

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

Thanks a lot!

[Matt_Jastremski]

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.