Cleaning address data

[dunlopoil]

Hi,
I have a database of sales records containing 6 address columns (C-H). I want to extract the country (which may be entered in any of the 6 columns) and place this in a separate column. I have a definitive list of countries on sheet 2 which I have saved as a named range (Country).

I would like to be able to set something up which searches the six address fields on sheet 1 and if a match is found against the Country range, enter this in a separate column (I). A non-code solution would be ideal as I am nowhere near that level yet and want to be able to understand what I am doing as far as possible!

Many thanks,
Doug.

 

[dunlopoil]

Blimey, 5.1Mb!

Anyway, the problem was easy, SPAIN in your CountryList is actually SPAIN+space. You could get rid of it, but there are many others, so this formula handles it, and commas and dots

=IF(ISNA(MATCH(TRUE,ISNUMBER(MATCH(SUBSTITUTE(SUBSTITUTE(TRIM(C2:H2),".",""),",",""),TRIM(CountryList),0)),0)),"",
INDEX(C2:H2,MATCH(TRUE,ISNUMBER(MATCH(SUBSTITUTE(SUBSTITUTE(TRIM(C2:H2),".",""),",",""),TRIM(CountryList),0)),0)))

I would add UK and USA to your list.

I've tried entering the formula this morning and it works well. The only thing I've noticed is that if, for example, the entry in the address fields is 'GREECE.' and the entry in the CountryList is 'GREECE', it enters 'GREECE.' in the new Country column. I would prefer the entry to be exactly the same as in the CountryList, to avoid duplication. Any ideas?

EDIT: I've just copied the formulas as values to a new column and removed any periods or commas with Edit/Replace.

 

[xld]

Just one thing- where do these stray spaces come from? I'm pretty certain that when I created the list I didn't enter a space after the country.

Is it just something you get into the habit of (using the TRIM formula) whenever you create a list like this?

I can't say, it does seem odd. But no, TRIM is an exceptiuon function in my mind, not a standard use one.

Is the following question now redundant?

 

[dunlopoil]

Yes, the following question was sorted easily enough by edit/replace- let's not over-complicate things!

I think the stray spaces may have appeared because I copied the list from the web. Anyway, I now know how to deal with it in future. Many thanks again for your help.