Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Formula result not being recognized as a number.

Solved
Jump to Solution
1893 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Tamas_Koncsik
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

Tamas,

I think this should work for you:

Screen Shot 2023-01-20 at 8.37.53 AM.png

The formula for the "Last three" field is simply

RIGHT(Name, 3) + 0

See Solution in Thread

5 Replies 5
Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

Tamas,

I think this should work for you:

Screen Shot 2023-01-20 at 8.37.53 AM.png

The formula for the "Last three" field is simply

RIGHT(Name, 3) + 0

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!

Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

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 - "

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!

Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

For anyone running into this in the future, the formula using a regular expression would be

REGEX_REPLACE(Name, "[^\\d]{1,3}","") + 0