Hello Community,
I need your help.
Some postal code is delivery in 24h and the other in 48h.
I need to create a formula.
If the postal code (on the column 
start by 94,92,91…, write 24h, if not write 48h.
Can you help me please ?
Thank’s,
Matthieu
Hey @Matthieu_LABERIBE!
So this is your desired result:
There are two ways to accomplish what you’re looking for.
Here’s what it looks like:
IF(
OR(
LEFT(
{Postal Code},
"2"
) = 94,
LEFT(
{Postal Code},
"2"
) = 92,
LEFT(
{Postal Code},
"2"
) = 91
),
"24h",
"48h"
)
This method extracts the first two numbers from the string (the data type must be a string in order for this to work) and tests whether it is equal to any of the numbers you want to look for.
So, for the postal code of 94103, if we extract the first two characters, we get 94.
Now, we’ll check to see if it matches one of the conditions in the formula.
This would be the snippet from the formula that matches it:
LEFT(
{Postal Code},
"2"
) = 94
The problem with this method is scalability.
Since you need to create a new possible condition for each possible postal code that fits the 24h shipping condition, you can quickly end up with a massive formula.
That brings us to:
This method will resolve the size and scalability issue with solving your use case.
It also means that you can easily add in conditions for different postal codes besides postal codes that are in the 91, 92, 93, etc. format.
Here’s what the formula would look like:
IF(
{Postal Code},
IF(
REGEX_MATCH(
{Postal Code},
'^9\\d'
),
"24h",
"48h"
)
)
Here, we’re using regex to search for a pattern:
^9\\d
This will look for:
So, regardless of the number, as long as it matches the pattern we provide, it will return the desired 24h result.
Regex can be intimidating, and Airtable’s regex syntax rules are finicky, so at the end of the day, go for the method you find best suits you.
Both have advantages and disadvantages.
Definitely let me know if you have any questions or concerns about anything I’ve written above!
Hey @Matthieu_LABERIBE!
So this is your desired result:
There are two ways to accomplish what you’re looking for.
Here’s what it looks like:
IF(
OR(
LEFT(
{Postal Code},
"2"
) = 94,
LEFT(
{Postal Code},
"2"
) = 92,
LEFT(
{Postal Code},
"2"
) = 91
),
"24h",
"48h"
)
This method extracts the first two numbers from the string (the data type must be a string in order for this to work) and tests whether it is equal to any of the numbers you want to look for.
So, for the postal code of 94103, if we extract the first two characters, we get 94.
Now, we’ll check to see if it matches one of the conditions in the formula.
This would be the snippet from the formula that matches it:
LEFT(
{Postal Code},
"2"
) = 94
The problem with this method is scalability.
Since you need to create a new possible condition for each possible postal code that fits the 24h shipping condition, you can quickly end up with a massive formula.
That brings us to:
This method will resolve the size and scalability issue with solving your use case.
It also means that you can easily add in conditions for different postal codes besides postal codes that are in the 91, 92, 93, etc. format.
Here’s what the formula would look like:
IF(
{Postal Code},
IF(
REGEX_MATCH(
{Postal Code},
'^9\\d'
),
"24h",
"48h"
)
)
Here, we’re using regex to search for a pattern:
^9\\d
This will look for:
So, regardless of the number, as long as it matches the pattern we provide, it will return the desired 24h result.
Regex can be intimidating, and Airtable’s regex syntax rules are finicky, so at the end of the day, go for the method you find best suits you.
Both have advantages and disadvantages.
Definitely let me know if you have any questions or concerns about anything I’ve written above!
That could also be written as:
IF(
{Postal Code},
SWITCH(
LEFT({Postal Code}, 2),
94, "24h",
92, "24h",
91, "24h",
"48h"
)
)
That could also be written as:
IF(
{Postal Code},
SWITCH(
LEFT({Postal Code}, 2),
94, "24h",
92, "24h",
91, "24h",
"48h"
)
)
Oooo!
I hadn’t thought of this method!
Thanks for calling it out! :grinning_face_with_smiling_eyes:
Thank’s a lot,
I understand the formula, you help me a lot.
Have a good week end,
Best regards,
Matthieu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.