Expert: Bill Hermanson - 5/1/2008

Question
Hi Bill,

I'm currently trying to manipulate some data using the IF function in excel.  I have a column that is a list of cities and I'm trying to assign a state to them through the function.  For instance, I want to be able to have the program look up "San Diego" and return "CA".  The problem I have is that the column with the data has more that just the city name in it, it has numbers following the city name.  Therefore, I'm trying to figure out a way to use the
"contains" function along with the "IF" function so that I can say IF column A contains "San Diego" then A=CA.  Is there any way to do this?  Please let me know.

Thanks,

owen

Answer
Owen,

 My version of Excel (2003) doesn't have a 'contains' function, so I don't know what that is.  Perhaps you meant FIND or SEARCH?

 I would propose the following method:

  1) use simple text processing to take out the numbers from the city name text.  This will be easy of the numbers are separated from the name by a space, dash, or other character.  If a space is used, you will have to be sure you find the LAST occurrence of the space in the text string to avoid cutting odd the second half of a two-word city name.  if there are ALWAYS the same number of digits, it will be even easier because you can just truncate them.

  2) create a list of all possible city names in one columns and the associated state in an adjacent column.  To create the list of city names, you could use a pivot table or an advanced filter set to reject duplicates.  Use your processed text strings as inputs.  After you get the list of city names then you will have to add the state abbrev's to the second column by hand.

  3) use a VLOOKUP function with the extracted city name as the input cell.  The 'table array' is the two-column set you created in step 2.  This will look up the state that corresponds with each city.

  If you do this exactly as I outlined, it is for sure going to work.  The trickiest part is the text equations that remove the numbers from the city name.

  If you can't handle this, I would create this for you for $75.  This is what I do for a living, make neat and interesting Excel spreadsheets.  Just give the word and send me your data set, and I will fix it all up for you and make it work just the way you want!  If you are interested in this opportunity, write to me at EXPERTATEXCEL@AOL.COM and we can get your project underway.

  I hope I've helped you out.

    >>> Bill