# Excel formula to enter country name identifying the cities



## VN5 (Jun 16, 2015)

Hi,

I need help with a formula which will identify the city name (contains full name or part) (I can make a spreadsheet of all US cities) and name it as USA in the country field.

For e.g. if the city says Baltimore Area / Greater Chicago and if I have Baltimore / Chicago in the list, it should enter USA in the country field. 

Thanks.

Vik


----------



## macropod (Apr 11, 2008)

What happens if the city also exists outside the US? As for partial matches, that's way beyond what a formula can reasonably be expected to handle.


----------



## VN5 (Jun 16, 2015)

If the area falls outside the US, the city field actually lists the country name there (in my previous e.g. instead of Baltimore Area / Greater Chicago, it will be France / Iran etc). So if I use a formula and identify US cities and get USA in the country column, all non US will be blank and I can simply copy the names in the country column.

If I can't get partial matches, its fine. I can make a spreadsheet which not only contains the city names, but also has all the possible variations of those cities.

Thanks.


----------



## macropod (Apr 11, 2008)

So, in the case of overseas cities, your city field would contain, for example, 'Paris, France' and there'd be no risk of an overseas Paris entry being confused with 'Paris, Texas', in the US? If so and the overseas cities always have a comma between the city & country names, you could probably use a formula like:
=IF(ISERROR(FIND(",",A1)),"USA",TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))))
to output all countries, or:
=IF(ISERROR(FIND(",",A1)),"USA","")
to output just 'USA' for the remainder - you wouldn't need a lookup table.


----------



## VN5 (Jun 16, 2015)

Hi,

Thanks for the formula. But as its based on the "," it will not work in my case.

If the area is non-US, the city field will only contain 'country name' so we will not have 'Paris' in the city name, but will have 'France'. There will be no "," between city and country name. The field will only have one value, either the 'city name' if US, or the 'country name' if non-US.


----------



## macropod (Apr 11, 2008)

So how do you propose to deal with countries whose names are the same as US cities (e.g. Columbia, IN; Israel, OH)?

In any event, a lookup list of foreign countries (~257 - List of countries of the world in alphabetical order) would likely be far shorter and easier to maintain than a list of US cities and their regions (i.e. Baltimore Area / Greater Chicago). In that case a simple lookup could be used to check the country list and, subject to the above caveat re cities with the same names as countries, to conditionally output 'USA', thus:
=IF(ISERROR(MATCH(A1,Countries,0)),"USA","")
where 'Countries' is a named range containing the list of countries.


----------

