Discover what data silos are costing your org in our commissioned Forrester study. Learn more
Aug 07, 2021 03:24 AM
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}
)
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:
Solved! Go to Solution.
Aug 07, 2021 07:17 AM
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+'))
Aug 07, 2021 04:22 AM
Hi TH. I would give this a try…
VALUE(REGEX_EXTRACT({Title},’\d+\.\d+’))
Let me know if it works. Good luck.
Aug 07, 2021 04:44 AM
Hi augmented,
Thanks for the swift reply. I copied and pasted the formula but it displayed that the formula was invalid.
Aug 07, 2021 04:52 AM
Just FYI. Copy/paste from these boards will often mess up your quotes. Make sure they are straight '
and not curly ’.
Aug 07, 2021 05:10 AM
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?
Aug 07, 2021 05:17 AM
Sorry, didn’t know that you had both. Try this…
VALUE(REGEX_EXTRACT({Title},'\\d+\\.?\\d+'))
Aug 07, 2021 05:28 AM
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?
Aug 07, 2021 06:42 AM
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.
Aug 07, 2021 07:07 AM
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?
Aug 07, 2021 07:17 AM
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+'))
Aug 07, 2021 07:29 AM
Mission Accomplished!
Thanks, you did it!