Skip to main content
Solved

Formular to Extract Numbers out of a string

  • October 21, 2024
  • 5 replies
  • 45 views
M
Forum|alt.badge.img+13

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 

Best answer by Alexey_Gusev

Hi, thx for the answer. 

I found a solution. Here the formular: 

 

Best

Mirko 


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

Alexey_Gusev
Forum|alt.badge.img+25

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.

 

M
M
Forum|alt.badge.img+13
  • mirkolando
  • Author
  • Inspiring
  • October 22, 2024

Hi, thx for the answer. 

I found a solution. Here the formular: 

 

Best

Mirko 

Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • Answer
  • October 22, 2024

Hi, thx for the answer. 

I found a solution. Here the formular: 

 

Best

Mirko 


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'

M
Forum|alt.badge.img+13
  • mirkolando
  • Author
  • Inspiring
  • October 22, 2024

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'


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

Thanks a lot!

Alexey_Gusev
M
Forum|alt.badge.img+9
  • Matt_Jastremski
  • Participating Frequently
  • October 24, 2024

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

Thanks a lot!


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.

Terms & ConditionsAccessibility statement