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

How do I reformat "City, State" into separate columns of City and State in Excel?

The following data exists in one cell:

City/State
Washington, DC
Washington, DC
Washington, DC
Washington, DC
Washington, DC
Arlington, VA
Arlington, VA
Springfield, VA
Springfield, VA
Springfield, VA
Washington, DC
Newington, VA
Lorton, VA
Fairfax, VA

I need to format it so the City information is in one cell and the state abbreviation is in another.
0
impala6
Asked:
impala6
3 Solutions
 
Eric CCommented:
Easy. In Excel 2007(?) and higher, there is a button called 'Text To Columns'

1. Highlight the entire column (single click on the column letter)
2. In the Data tab, click the 'Text to Columns' button
3. In the dialog box, deselect 'Tab' and select 'Comma'
4. Click 'Next'

See screen shot.
text-to-columns.jpg
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please follow these steps...

1) Select the column with City/State strings

2) Make sure that the next adjacent column is blank, if not, insert a new column.

3) Go to Data Tab --. Text to Columns

4) Choose Delimited --> Next --> Check the checkbox for comma --> Next --> Finish
1
 
Naresh PatelTraderCommented:
Text To ColumnNext - Next FinishThanks
0
Independent Software Vendors: 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!

 
efrimpolCommented:
Column A, Column B, Column C - Record starts Cell A2

Column A = "Washington, DC"
Column B = "=LEFT(A2, SEARCH(",",A2,1))"
Column C = "=RIGHT(A2,LEN(A2)-SEARCH(",",A2,1))"

Results in Column B2 = "Washington"
Results in Column C2 = "DC"
1
 
efrimpolCommented:
Typo - Record starts in Cell A1, not A2
1
 
Naresh PatelTraderCommented:
zings too fast man....i am just about creating snaps & posting....   :)   after posting i see two post by others...
0
 
efrimpolCommented:
see screenshot
0
 
efrimpolCommented:
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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