The dataset includes the name of cities and states.
Method 1 – Using the Excel Text to Columns Wizard
The Text to Columns feature separates a group of the text string from a cell into multiple cells based on a delimiter.
Steps:
- Add two columns: City and State.
- Copy the data in the Address column pressing Ctrl+C. Paste it into the City column pressing Ctrl+V.
- Select the City column.
- Go to the Data tab.
- Choose Text to Columns in Data Tools.
- The Convert Text to Columns Wizard window will be displayed.
- Check Delimited and click Next.
- In the 2nd window of Convert Text to Columns Wizard, check Comma and Space.
- Click Next.
- In the 3rd window, choose Text.
- Click Finish.
- In the warning window, click OK.
- This is the output.
City and State names are separated.
Read More: How to Separate City and State without Commas in Excel
Method 2 – Applying the Flash Fill Feature
The Flash Fill fills a column or rows automatically following a pattern.
Steps:
- Enter city and state manually in the first two cells columns C and D..
- Select all the cells in the City column.
- Go to the Data tab.
- Choose Flash Fill in Data Tools.
- This is the output.
- Select the cells in the State column.
- Press Ctrl+E.
- This is the output.
Read More: How to Separate Address Number from Street Name in Excel
Method 3 – Combining the RIGHT, LEFT, FIND, and LEN Functions to Separate City and State
Steps:
- Go to C5 in the City column.
- Use the following formula:
=LEFT(B5,FIND(",",B5)-1)
- Press Enter.
The city name is displayed.
- Drag down the Fill Handle to see the result in the rest of the cells.
- To get the State names, go to D5 and enter the following formula.
=RIGHT(B5,LEN(B5)-FIND(",",B5)-1)
- Press Enter button and drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
Formula Breakdown
- FIND(“,”,B5)
Searches comma (,) in B5 and shows the position.
Result: 11
- FIND(“,”,B5)-1
Subtracts 1 from the previous result.
Result: 10
- LEFT(B5,FIND(“,”,B5)-1)
shows 10 characters from the left side.
Result: Montgomery
- LEN(B5)
shows the length of B5.
Result: 19
- LEN(B5)-FIND(“,”,B5)-1)
performs a subtraction.
Result: 7
- RIGHT(B5,LEN(B5)-SEARCH(“,”,B5)-1)
Shows 7 characters from the right side.
Result: Alabama
Read More: How to Split Inconsistent Address in Excel
Download Practice Workbook
Download the practice workbook.
Related Articles
- How to Separate Address in Excel
- How to Separate Address in Excel Using Formula
- How to Separate Address in Excel with Comma
- How to Separate City State and Zip from Address Using Excel Formula
<< Go Back to Address Format | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
