• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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

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.
Top100Cities.xlsx
0
ruavol2
Asked:
ruavol2
  • 4
4 Solutions
 
jppintoCommented:
1) you can use this formula: =MID(B2,1,FIND("(",B2)-2)

see attached file.

Top100Cities.xlsx
0
 
jppintoCommented:
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?
0
 
jppintoCommented:
Like this?

"New York", "New York"
0
 
jppintoCommented:
Actually, for 1) you should use this formula instead because you a space on the beginning of each row:

=TRIM(MID(B2,1,FIND("(",B2)-2))
0
 
ruavol2Author Commented:
Thank you very much.
0

Featured Post

Sign your company up to try the MB 660 headset now

Take control and stay focused in noisy open office environments with the MB 660. By reducing background noise, you can revitalize your office and improve concentration.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now