Re: Formula IF word start by

Matthieu_LABERI · ‎Jun 30, 2022

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 B) start by 94,92,91…, write 24h, if not write 48h.

Can you help me please ?

Thank’s,

Matthieu

Ben_Young1 · ‎Jun 30, 2022

Hey @Matthieu_LABERIBE!

So this is your desired result:

There are two ways to accomplish what you’re looking for.

Method One: The LEFT() Function

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:

Method Two: Regular Expressions

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:

^
- The start of a string.
9
- This will look for the number 9 exclusively.
\\d
- Any digit between 0-9.

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!

Kamille_Parks · ‎Jun 30, 2022

That could also be written as:

IF(
  {Postal Code},
  SWITCH(
    LEFT({Postal Code}, 2),
    94, "24h",
    92, "24h",
    91, "24h",
    "48h"
  )
)

Ben_Young1 · ‎Jun 30, 2022

Oooo!
I hadn’t thought of this method!

Thanks for calling it out! :grinning_face_with_smiling_eyes:

Matthieu_LABERI · ‎Jul 06, 2022

Thank’s a lot,
I understand the formula, you help me a lot.
Have a good week end,
Best regards,

Matthieu