Hi there,
I have a table with a Name field and in that field (in different variations and location in the text string) is a series number as either 001 or 1.
In a Series No. field I am including a formula that extracts just the series number.
I am trying to find a solution to write a more elegant formula solution that does a FIND against the Name field and if it find a number from 1 to 100 then it will return that value in the Series No. field.
Here is the formula I have at the moment but I want to avoid having to replicate this out from 1 to 100. I know there must be an easier way to do this but just don’t know how to go about it, especially in Airtable.
IF(OR(FIND(“Series 001”,Name), FIND(“Series 1”,Name)),“001”, IF(OR(FIND(“Series 002”,Name), FIND(“Series 2”,Name)),“002”, IF(OR(FIND(“Series 003”,Name), FIND(“Series 3”,Name)),“003”, IF(OR(FIND(“Series 004”,Name), FIND(“Series 4”,Name)),“004”, IF(OR(FIND(“Series 005”,Name), FIND(“Series 5”,Name)),“005”)))))
Any thoughts?
