Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 20, 2023 12:44 AM
Hi All,
I have text field called Name, which contains data in the following format: XXX - YYY - ###, where XXX and YYY are random texts, ### is a 1/2/3 digit number and they are always separated by " - ".
In another field I'm using the following formula: TRIM(MID(Name, FIND("-",Name,FIND("-",Name)+1)+1,3)).
My goal is to find the number at the end of the Name field and use it as a number in other calculations, but although the result looks good, it is not recognized as a number.
Any suggestions?
Thank you in advance!
Solved! Go to Solution.
Jan 20, 2023 05:38 AM
Tamas,
I think this should work for you:
The formula for the "Last three" field is simply
Jan 20, 2023 05:38 AM
Tamas,
I think this should work for you:
The formula for the "Last three" field is simply
Jan 20, 2023 05:55 AM
Hi Ron,
the issue with RIGHT(Name, 3) is that I have 1 and 2 digit numbers as well, so i.e. for XXX - YYY - 1, it would give "- 1" as a result. BUT I've used your suggestion to add "+0" to the end of my formula and it worked! There are some weird things in Airtable for sure.....
Thank you very much!
Jan 20, 2023 06:01 AM
D'oh! I missed the part where you said "### is a 1/2/3 digit number". That's what I get for trying to read first thing in the morning LOL!
Note: If you wanted to have a shorter formula, you could also use REGEX_REPLACE() to get rid of the "XXX - YYY - "
Jan 20, 2023 06:07 AM
No worries 🙂 yes, maybe I could play with a bit, to at least replace the remaining "-"s in the result, but as XXX and YYY are random texts, I'm not sure how that would work with replace. But luckily mine works quite stable at the moment, so I will just jump on the next issue 😉 Thank you again!
Jan 20, 2023 11:28 AM
For anyone running into this in the future, the formula using a regular expression would be