Look up a word within a sentence and copy into a next column [Closed]

Report
-
Posts
1
Registration date
Monday February 20, 2017
Status
Member
Last seen
February 23, 2017
-
Hello,

I have an excel file with over 10 000 entries, each entry contains a city within the sentence - I also have a database (another sheet) with all the cities in the world - is there a way to look up these cities within the column and add only the city from the column with sentence to the column next to it - so I basically need to take out only the city from the sentence I have with the help of the database of cities - would some kind of vlook up work?

Thank you so much!

2 replies

Posts
1875
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
February 9, 2021
139
Erika, Good morning.

Since you have not told us the layout of your spreadsheet, it's harder to conclusively help you.

But I'll try to help you.

Suppose this scenario:

Sheet1
A1:A10000 --> Your sentences
B1:B10000 --> FORMULA --> your future city names

Sheet2
A1:A200000 --> Data base of City Names.

Try to use:

Sheet1

B1 --> =LOOKUP(2^15, FIND(Sheet2!$A$1:$A$200000, A1), Sheet2!$A$1:$A$200000)

Copy it down.

Is that what yo want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Thank you Mazzaropi for trying to help. Yes, you got the scenario right, but unfortunately the formula is not working, even the FIND part of the formula is not retrieving the city.

Thank you for trying anyway

All the best
Erika
Posts
1875
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
February 9, 2021
139
Erika, Good afternoon.

Here is an small example of your data using my suggested formula:
https://www.sendspace.com/file/5i1ty1

In this case, the formula has been treated so that it does not display an error when no city is found in the sentence or the cell is empty.

Is that what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Posts
1
Registration date
Monday February 20, 2017
Status
Member
Last seen
February 23, 2017

wow, thank you so much Mazzaropi, that is great! This is exactly what I was looking for!

Another question....it becomes a bit more difficult if there are two cities in the sentence...

e.g. - I'm going to Paris from Vienna - so let`s say I would want preference to be given the end word and only then display Vienna in the next column - is that possible?

Thank you for all your time and your help

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!