Hi

I am looking for a formula that can assist with the following:

I have a string in a cell that is an address e.g. ALEXANDER MEWS 1 GARSFONTEIN. I also have a list of whole bunch of suburbs on another excel spreadsheet. I would like Excel to search within the cell containing the address and if it find a suburb (that exists withing my suburb column, to then return that suburb on the adjacent cell. An added function would be if it could extract it, and then return the extracted value in the adjacent cell. Not sure if this can be done. I was playing around with the following example formula, but it is not working:



=IF(ISNUMBER(SEARCH(list of suburbs,A2)),"list of suburbs,suburbs,"")

Hope someone can assist? I have about 5000 cells to check...thank you.