Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Extract numerical value from string

Topic Labels: Formulas
Solved
Jump to Solution
1150 10
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi folks,

Airtable - Extract numerical value from single line text.jpeg

I’m trying to find a numerical value from the “Title” field and have it appear in the “amount” (e.g. 27.21)

I’ve used this formula from looking at a few post’s but it only copies the values before the decimal:

MID(
{Title},
FIND(
“For”,
{Title}
)

  • 6, 50
    )

Another formula I found only copies the values before the decimal:

IF({Title}, VALUE(RIGHT({Title}, LEN({Title})- FIND(“0”,“1”,“2”,“3”,“4”,“5”,“6”,“7”,“8”,“9”, {Title}))), 0)

What formula do I need to get the full total which in this example is 27.21?

Thanks in advance!

:winking_face:

1 Solution

Accepted Solutions

Sorry. You’re running up against my relative noobish-ness wrt regex. This is the last thing I have.

VALUE(REGEX_EXTRACT({Title},'\\d*\\.?\\d+'))

See Solution in Thread

10 Replies 10

Hi TH. I would give this a try…

VALUE(REGEX_EXTRACT({Title},’\d+\.\d+’))

Let me know if it works. Good luck.

Hi augmented,

Thanks for the swift reply. I copied and pasted the formula but it displayed that the formula was invalid.

Just FYI. Copy/paste from these boards will often mess up your quotes. Make sure they are straight ' and not curly ’.

Thanks, that was the problem which is now solved.

BUT I now find that only works for 2 decimal place numbers e.g. 27.21
my new problem is that it doesn’t work for example double-digit numbers e.g 27.

What would be the formula for finding both types?

Sorry, didn’t know that you had both. Try this…

VALUE(REGEX_EXTRACT({Title},'\\d+\\.?\\d+'))

No worries, I don’t think I was clear enough. This formula works

but it now copies whole numbers as they are which is correct e.g. 48 would be 48 thanks to your formula. The only problem now is it rounds to the closest whole number for two decimal point numbers e.g. 14.89 is now 15 due to the formula.

I need 14.89 to still be 14.89

can a formula do both number types as the output?

Check your “Formatting” tab right next to your “Formula” tab (it has the box where you enter the formula). You probably need to adjust it. Here is my formula working for both types.

image

Thanks! that was my bad. I just need to adjust your current formula to also work for single digits.

What would need to be added?

Sorry. You’re running up against my relative noobish-ness wrt regex. This is the last thing I have.

VALUE(REGEX_EXTRACT({Title},'\\d*\\.?\\d+'))

Mission Accomplished!

Thanks, you did it!