Skip to main content

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.

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
)
)
)
)
)
)
)
)
)

 

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?

Reply


Terms & Conditions