I have a field of cardinal numbers that I want to use in a concat formula. I want to to convert the cardinal number to ordinal number in the concat formula. I tried to use an excel solution but it uses a formula which is not supported in airtable. The choose function.
+20Welcome to the community, Micah! :grinning_face_with_big_eyes: One thing you didn’t specify is whether your cardinal numbers are numeric (1, 3, 5) or text (one, three, five). If it’s the former, you can tap into Airtable’s datetime processing functions for the conversion:
The formula in the {Ordinal} field is:
DATETIME_FORMAT(DATETIME_PARSE(Cardinal, "DDD"), "DDDo")
The only downside is that this only works with numbers up to 365, as the functions are only designed to work with dates. Any number higher than 365 will result in #ERROR.
If this solution won’t get you where you want to go, could you provide a detailed example of your cardinal source material, and what you’re attempting to do via concatenation?
1 person likes this
Justin_Barrett wrote:
Welcome to the community, Micah! :grinning_face_with_big_eyes: One thing you didn’t specify is whether your cardinal numbers are numeric (1, 3, 5) or text (one, three, five). If it’s the former, you can tap into Airtable’s datetime processing functions for the conversion:
The formula in the {Ordinal} field is:
DATETIME_FORMAT(DATETIME_PARSE(Cardinal, "DDD"), "DDDo")
The only downside is that this only works with numbers up to 365, as the functions are only designed to work with dates. Any number higher than 365 will result in #ERROR.
If this solution won’t get you where you want to go, could you provide a detailed example of your cardinal source material, and what you’re attempting to do via concatenation?
Hi Justin thank you for the reply. I am working with numeric numbers not text and the numbers won’t get over 30 so this should work great. Thanks a bunch!
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.