Skip to main content

I have a column that shows a person’s preferred class location and a column that shows their preferred class time. I want a formula that takes the values from each of these columns to provide a value in the 3rd column for example:



If preferred location is Chapel A and preferred time is evening, enter value in 3rd column of “Chapel A Evening Waiting List”



If preferred location is Chapel A and preferred time is Daytime, enter value in 3rd column of “Chapel A Daytime Waiting List”



If preferred location is City Centre and preferred time is evening, enter value in 3rd column of “City Centre Evening Waiting List”



If preferred location is City Centre and preferred time is Daytime, enter value in 3rd column of “City Centre Daytime Waiting List”



If preferred location is Hyde Park and preferred time is evening, enter value in 3rd column of “Hyde Park Evening Waiting List”



If preferred location is Hyde Park and preferred time is Daytime, enter value in 3rd column of “Hyde Park Daytime Waiting List”



Is this possible?

Hi @Ben_Green - you don’t really need an IF/AND formula for this, just something that joins the two values together:



Location & ' ' & {Preferred time} & ' Waiting List'





JB

Hi @Ben_Green - you don’t really need an IF/AND formula for this, just something that joins the two values together:



Location & ' ' & {Preferred time} & ' Waiting List'





JB


That’s great. Thanks so much @JonathanBowen 🙂


The issue I have now is that Airtable is pulling the data in from FB Lead form and rather than the values coming in with a capital at the start and a clear space in between words they are coming in as location_a e.g. city_centre and evening


I need the 3rd column to display them as City Centre Evening Waiting List so that when it feeds into my CRM it matches the tag that triggers the appropriate e-mail sequence.



How do I make sure the incoming format gets converted to Title Case with no underscores?

hi @Ben_Green - you could do this in a single formula field, but it would be pretty messy. The solution I’ve come up with uses multiple fields to make the formulas a bit simpler to manage. Obviously, you can hide interim fields and just show the final one.



Take a look at this base:









However, my solution only works if your locations is a single word, e.g. “london” or two words with an underscore separator, e.g. “hyde_park” - it won’t work if there are 3 or more words, e.g. “the_hyde_park”



Overall, it is a pretty messy set of formulas to get to a title case function - others on the forum might have a better routine to achieve the same.



JB

(copy the base to your workspace and you will be able to see the formulas)

hi @Ben_Green - you could do this in a single formula field, but it would be pretty messy. The solution I’ve come up with uses multiple fields to make the formulas a bit simpler to manage. Obviously, you can hide interim fields and just show the final one.



Take a look at this base:









However, my solution only works if your locations is a single word, e.g. “london” or two words with an underscore separator, e.g. “hyde_park” - it won’t work if there are 3 or more words, e.g. “the_hyde_park”



Overall, it is a pretty messy set of formulas to get to a title case function - others on the forum might have a better routine to achieve the same.



JB


Wow! This is amazing. Thank you so much @JonathanBowen. It works perfectly. 😀 😀 😀

Reply


Terms & Conditions