Help

Nested IF/IF not working for this beginner

Topic Labels: Formulas
Solved
Jump to Solution
336 2
cancel
Showing results for 
Search instead for 
Did you mean: 
AlexiosKit
4 - Data Explorer
4 - Data Explorer

I have a column with the title 'Country', and I am trying to add a new formula column which identifies the Country and creates an entry for the Region automatically. I can get the first IF function to select one region, but when I try to add the second, it fails. Here is what I have for 3 (of the shorter regions) of the 8:

IF(REGEX_MATCH({Country}&"", "Belize|Costa Rica|El Salvador|Guatemala|Honduras|Nicaragua|Panama"),"Central America")

IF(REGEX_MATCH({Country}&"", "Canada|Greenland|Mexico|Saint Pierre and Miquelon|United States"),"North America")

IF(REGEX_MATCH({Country}&"", "Argentina|Bolivia|Brazil|Chile|Colombia|Ecuador|Falkland Islands|French Guiana|Guyana|Paraguay|Peru|Suriname|Uruguay|Venezuela"),"South America")

Thank you.

1 Solution

Accepted Solutions
Ben_YoungV2
5 - Automation Enthusiast
5 - Automation Enthusiast

You have a couple of options. It depends on what you want your formatting to look like, as well as the field type of your Country field.

Because you've added the empty string with the reference to the Country field, I presume it's a linked record field. I'll assume that you only anticipate that a single country will be returned at a time.

IF(
    {Country},
    IF(
        REGEX_MATCH(
            LOWER({Country}) & '',
            '(belize|costa\srica|el\ssalvador|guatemala|honduras|nicaragua|panama)'
        ),
        'Central America',
        IF(
            REGEX_MATCH(
                LOWER({Country}) & '',
                '(canada|greenland|mexico|saint\spierre\sand\smiquelon|united\sstates)'
            ),
            'North America',
            IF(
                REGEX_MATCH(
                    LOWER({Country}) & '',
                    '(argentina|bolivia|brazil|chile|colombia|ecuador|falkland\sislands|french\sguiana|guyana|paraguay|peru|suriname|uruguay|venezuela)'
                ),
                'South America'
            )
        )
    )
)

Because of the length of this formula, I might recommend a different pattern to make the formula easier to maintain and read, potentially avoiding tech debt. Regardless, here's a templatized version for the eight regions that you can just copy and paste your existing regex and desired values into.

I've commented the specific areas to insert your regular expression parameters and your region string value.

IF(
    {Country},
    IF(
        REGEX_MATCH(
            LOWER({Country}) & '',
            '' // Countries
        ),
        '', // Region
        IF(
            REGEX_MATCH(
                LOWER({Country}) & '',
                '' // Countries
            ),
            '', // Region
            IF(
                REGEX_MATCH(
                    LOWER({Country}) & '',
                    '' // Countries
                ),
                '', // Region
                IF(
                    REGEX_MATCH(
                        LOWER({Country}) & '',
                        '' // Countries
                    ),
                    '', // Region
                    IF(
                        REGEX_MATCH(
                            LOWER({Country}) & '',
                            '' // Countries
                        ),
                        '', // Region
                        IF(
                            REGEX_MATCH(
                                LOWER({Country}) & '',
                                '' // Countries
                            ),
                            '', // Region
                            IF(
                                REGEX_MATCH(
                                    LOWER({Country}) & '',
                                    '' // Countries
                                ),
                                '', // Region
                                IF(
                                    REGEX_MATCH(
                                        LOWER({Country}) & '',
                                        '' // Countries
                                    ),
                                    '' // Region
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

 

See Solution in Thread

2 Replies 2
Ben_YoungV2
5 - Automation Enthusiast
5 - Automation Enthusiast

You have a couple of options. It depends on what you want your formatting to look like, as well as the field type of your Country field.

Because you've added the empty string with the reference to the Country field, I presume it's a linked record field. I'll assume that you only anticipate that a single country will be returned at a time.

IF(
    {Country},
    IF(
        REGEX_MATCH(
            LOWER({Country}) & '',
            '(belize|costa\srica|el\ssalvador|guatemala|honduras|nicaragua|panama)'
        ),
        'Central America',
        IF(
            REGEX_MATCH(
                LOWER({Country}) & '',
                '(canada|greenland|mexico|saint\spierre\sand\smiquelon|united\sstates)'
            ),
            'North America',
            IF(
                REGEX_MATCH(
                    LOWER({Country}) & '',
                    '(argentina|bolivia|brazil|chile|colombia|ecuador|falkland\sislands|french\sguiana|guyana|paraguay|peru|suriname|uruguay|venezuela)'
                ),
                'South America'
            )
        )
    )
)

Because of the length of this formula, I might recommend a different pattern to make the formula easier to maintain and read, potentially avoiding tech debt. Regardless, here's a templatized version for the eight regions that you can just copy and paste your existing regex and desired values into.

I've commented the specific areas to insert your regular expression parameters and your region string value.

IF(
    {Country},
    IF(
        REGEX_MATCH(
            LOWER({Country}) & '',
            '' // Countries
        ),
        '', // Region
        IF(
            REGEX_MATCH(
                LOWER({Country}) & '',
                '' // Countries
            ),
            '', // Region
            IF(
                REGEX_MATCH(
                    LOWER({Country}) & '',
                    '' // Countries
                ),
                '', // Region
                IF(
                    REGEX_MATCH(
                        LOWER({Country}) & '',
                        '' // Countries
                    ),
                    '', // Region
                    IF(
                        REGEX_MATCH(
                            LOWER({Country}) & '',
                            '' // Countries
                        ),
                        '', // Region
                        IF(
                            REGEX_MATCH(
                                LOWER({Country}) & '',
                                '' // Countries
                            ),
                            '', // Region
                            IF(
                                REGEX_MATCH(
                                    LOWER({Country}) & '',
                                    '' // Countries
                                ),
                                '', // Region
                                IF(
                                    REGEX_MATCH(
                                        LOWER({Country}) & '',
                                        '' // Countries
                                    ),
                                    '' // Region
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

 

AlexiosKit
4 - Data Explorer
4 - Data Explorer

I have just encountered another issue with this formula; If it looks only at country to determine region, it defines the region, but if a user creates a record (field:'Comment'), lets say "Here is Mexico it is hot" and puts Peru as the Country by mistake, is it possible to have this IF formula check against more than one field?