I have a list of Citys with not state. I am needing to use the external file provided to find the city and populate the state. I have tried using Find and Replace but is didn't work. City StateMILWAUKEE KENT LAKE FOREST NEW BERLIN SEATTLE OAK CREEK SHOREWOOD WATERTOWN WESTBEND uscities_City_State_Zip.csv

Find city name in file, return state name. Using two files

I have a list of Citys with not state. I am needing to use the external file provided to find the city and populate the state. I have tried using Find and Replace but is didn't work.

City	State
MILWAUKEE
KENT
LAKE FOREST
NEW BERLIN
SEATTLE
OAK CREEK
SHOREWOOD
WATERTOWN
WESTBEND

uscities_City_State_Zip.csv

Topic of Interest

Predictive Analysis

Accepted answers

SPetrie

Did you use "Case insensitive find" with your find/replace?

Another thing to consider is that find/replace will only update with the last found value, so even if a city matches multiple entries, it will only output the last match. Join would show you all the matches, you just need to convert the state/zip file to caps first.

Also, you have Westbend in your sample set, but in the file its two words, so it would not match either.

All comments

BrandonB

How do you plan on dealing with scenarios where the same city name exists in multiple states?

lbolin

I would say leave it blank. I am not looking for 100% coverage but any I can get is a plus.

SPetrie

Did you use "Case insensitive find" with your find/replace?

Also, you have Westbend in your sample set, but in the file its two words, so it would not match either.

lbolin

What did you use to join on??

SPetrie

The City column from your sample list of 9 city names, and the city column of the .csv you provided. Before joining, I used trim and uppercase to match to the uppercase names from your list.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167