Extract numerical value from string

[davidcarldesign]

Hi folks,

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:

[augmented]

Hi TH. I would give this a try…

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

Let me know if it works. Good luck.

[davidcarldesign]

Hi augmented,

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

[augmented]

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

[davidcarldesign]

augmented:

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

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?

[augmented]

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

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

[davidcarldesign]

augmented:

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?

[augmented]

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.

[davidcarldesign]

augmented:

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

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?

[augmented · Accepted solution]

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+'))