Help

Re: Formula IF word start by

1514 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Matthieu_LABERI
6 - Interface Innovator
6 - Interface Innovator

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

4 Replies 4

Hey @Matthieu_LABERIBE!

So this is your desired result:

image

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!

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:

Matthieu_LABERI
6 - Interface Innovator
6 - Interface Innovator

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

Matthieu