How do I strip out the content in an Excel cell so that I can create and optimized list of words

Posted on 2011-03-15
Last Modified: 2013-11-19
I have an excel file I copied from a public source for this example. I was looking for a simple way to do two things in excel.
1. Remove the everything after the state (getting rid of the population information)
2. Replacing it back with a comma after the city and or forcing quotes around both city and State.

The purpose is to identify for me the cities that I would like to market my consulting services to. I already have clients in many of the listed cities and I wanted to make sure future potential customers are aware that I travel to their cities. So i wanted to make sure that the names of the cities I traveled to were listed in and on my site. I have many more favorite towns I frequent that I would like to add as well. Your support is greatly appreciated. Thank you.
Question by:ruavol2
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
LVL 33

Accepted Solution

jppinto earned 500 total points
ID: 35140376
1) you can use this formula: =MID(B2,1,FIND("(",B2)-2)

see attached file.

LVL 33

Assisted Solution

jppinto earned 500 total points
ID: 35140388
For 2), for instance, for the first line:

New York, New York (pop 8,213,839)

How would it look like after the transformation you want to do?
LVL 33

Assisted Solution

jppinto earned 500 total points
ID: 35140395
Like this?

"New York", "New York"
LVL 33

Assisted Solution

jppinto earned 500 total points
ID: 35140558
Actually, for 1) you should use this formula instead because you a space on the beginning of each row:


Author Closing Comment

ID: 35235794
Thank you very much.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question