Lookup city names in a string and then return the city code from a table

So, I have thousands of addresses - no consistent formatting ex:
Column 1...………………………………………………………………………………. Column2
123 Main Street, OLDS AB CANADA ……………………………………….Formula to return "OL"
345-2 st ATHABASCA DRIVE, ATHABASCA AB CANADA...……..Formula to return "AT1"


I have a table that provides the code ex:
City...............City Code
Olds......………...OL
Athabasca...…...AT1

I would like column2 on the first sheet to return the city code.

Thank you all who take a look!

See yellow banner at top of page on how to post a sample workbook.

Is format always "City" "Province" CANADA? province 2 characters ?

Try

=VLOOKUP(TRIM(MID($A2,FIND(",",$A2)+2,FIND("CANADA",$A2)-FIND(",",$A2)-5)),$F$2:$G$100,2,FALSE)

City/ City Code table in F2:G100

Hi,

The simplest would be:

=LOOKUP(1,0/COUNTIF(A2,"*"&table!A$2:A$42&"*"),table!B$2:B$42)

However, this is not certain to give correct results. For example, if A2 instead contained:

GOLDSTONE ROAD W, LETHBRIDGE AB CANADA

then the above formula would return "OL", not "LRPS", since "OLDS" is contained within "GOLDSTONE".

To avoid this, it's generally better practice to search for the strings surrounded at either side by a single space:

=LOOKUP(1,0/ISNUMBER(SEARCH(" "&table!A$2:A$42&" "," "&A2&" ")),table!B$2:B$42)

though this would fail for cases such as:

5100 65TH AVE ROCKY MOUNTAIN HOUSE, AB

since there is a comma after "ROCKY MOUNTAIN HOUSE".

So we can replace any commas with spaces:

=LOOKUP(1,0/ISNUMBER(SEARCH(" "&table!A$2:A$42&" "," "&SUBSTITUTE(A2,","," ")&" ")),table!B$2:B$42)

which will work providing other punctuation marks aren't also possible. If they are, you'll need to supply a definitive list of those punctuation which could occur within the addresses ('covering the bases' and adding clauses into the formula to account for all known punctuation would be overkill).

Regards