Formula result not being recognized as a number.

[Tamas_Koncsik]

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!

[Ron_Daniel · Accepted solution]

Tamas,

I think this should work for you:

The formula for the "Last three" field is simply

RIGHT(Name, 3) + 0

[Tamas_Koncsik]

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]

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

[Tamas_Koncsik]

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]

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

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