vlookup partial text in a given text string

Hello All!

I've been looking all over the internet but I haven't found the answer to my excel challenge yet. Basically what formula I require can be best explained with this example:

I have a row where I have adresses from all over the world. Like shown below:

FILE1:
Row 1
No.76 Kexue Ave. Zhengzhou High&New Technology Industries Development Zone, Zhengzhou, 450001 - China
478 Industrial Park Rd., Berlin, WI, 54956 - USA
Paterson, NJ, 07524-1112 - USA

(I highlighted the partI want to vlookup later in BOLD)
I have a second file, a database, with all cities from all over the world and I want to vlookup values there according to the city. Here an extract of my file

FILE2:
Row 1 | Row 2 | Row 3
Country | City | vlookup value I want
China - Zhengzhou - x8555
Germany - Berlin - b1858
USA - Berlin - KVG
USA - Paterson - 23213
USA - New York - ZHB
...

So basically what I need to do is vlookup the city in that cell that contains plenty of other information like zip codes etc.
My formula was:

=VLOOKUP("*"&A1&"*";Table in File 2;3;false)

Somehow this doesnt work?!

Second challenge is that city names are not unique, as you can see it with the example of "Berlin". I guess I could need to add the country code and combine it with the city, then lookup that value like:

USA-478 Industrial Park Rd., Berlin, WI, 54956 - USA

I hope my explanation was accurate enough... Do not hesitate to ask questions


Last but not least Text to column in FILE 1 is not and option (to isolate the city) because the setup for every country is diffrent and I have 200 diffrent countries...

Thanks very much!!

regards

I am sorry, I don't get your reply and don't understand what it has to do with my mentionned challenge..?

Thanks for clarifying,

Regards

Hi again,

Oh I am sorry I completely misundertood. Sure, see attached file, it should be more clear now.

Thanks for you help.

If the city name was always in a standardized place within the address (say, for example, immediately following the first comma) this would be quite easy to accomplish. However this doesn't seem to be the case. Are there any other formats that the addresses are available in?

Jeetheone,

I have almost been able to accomplish what you are after. I started by isolating the individual words of the addresses. Unfortunately, it seems that (on occasion) street names are being muddled in with city names, causing incorrect vlookups to occur. Take a look at the attached spreadsheet to see what I mean. Let me know if that has helped you at all?

Hi Guys

Thanks for your answers!
@Tung74: Unfortunately no, the City is never in the same position... which would have made it too easy Your submitted file looks not bad, indeed that could be one approach. However I would need it to be match as close as 100% as possible.

@Haseeb:
What does your vlookup value refer 9.9E+300 to? SInce I dont use US excel I would need to translate this. Or would it be possible to insert your formula in the attached file?

Many thanks guys this freaking problem is eating me up!!!

What I would suggest is to then use my method, but take the output column, copy it, and paste as VALUES into an adjacent column. Then Ctrl+f "error" within the column and correct unknowns by hand. Viable?

Hey Tung74,

thanks very much for your suggestions but unfortunately I need to imperatively be able to read out the city out of one single cell, no matter the complexity of the text string.

I am running out of ideas but I cant imagine that there is not such formula to vlookup only part of a text string.

I welcome any other suggestions / answers.

Thanks!