Alok Paul
Usually, an address consists of house number, street, city and state names, zip, etc. Typically, they are segmented by a symbol like Comma(,). For this article, we think of part an address which consists of city and state name. We want to separate the city and state in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Methods to Separate City and State in Excel
We will discuss 3 methods to separate city and state in Excel. Our dataset includes the name of cities and states.
1. Use Excel Text to Columns Wizard
The Text to Columns is a useful feature of Excel. This feature separates a group of the text string from a cell into multiple cells based on a delimiter.
In this section, we will use this Text to Columns tool to separate city and state.
📌 Steps:
- We added two columns named City and State to separate the address into them.
- Now, copy the data from the Address column using Ctrl+C. After that, paste them into the City column by pressing Ctrl+V.
- Select all the cells of the City column.
- Press the Data tab.
- Choose Text to Columns from the Data Tools group.
- The Convert Text to Columns Wizard window appears.
- Check the Delimited option and then click on the Next button.
- 2nd window of Convert Text to Columns Wizard appears now.
- Check the Comma and Space options.
- Again, click on the Next button.
- Lastly, 3rd window appears.
- Choose the Text option.
- Finally, hit the Finish button.
- A warning window appears. Press OK there.
- Look at the dataset.
City and State names are separated successfully.
Read More: How to Format Addresses in Excel (4 Easy Methods)
Similar Readings
- Formula to Create Email Address in Excel (2 Suitable Examples)
- How to Make an Address Book in Excel (An Ultimate Guide)
- Create Email Address with First Initial and Last Name Using Excel Formula
2. Apply Flash Fill Feature
The Flash Fill is a built-in feature of Excel. It fills a column or rows automatically following a pattern given by the user. It is available in Excel 2013 and later versions.
In this section, we will use this Flash Fill feature to separate addresses. We will show both ribbon and keyboard shortcuts for this method.
📌 Steps:
- First, we need to manually input city and state information on the City and State columns respectively.
- We insert information on the first two cells of both columns.
- Now, select all the cells of the City column.
- Go to the Data tab.
- Choose Flash Fill from the Data Tools group.
- Look at the dataset now.
The City column is filled with data. Now, we will show the keyboard shortcut.
- Similarly, we select the cells of the State column.
- Then, press the Ctrl+E.
- Again, look at the dataset.
The State column is also filled with desired data.
Read More: How to Separate Address Number from Street Name in Excel (6 Ways)
3. Combine RIGHT, LEFT, FIND, and LEN Functions to Separate City and State
In this section, we will use the combination of the LEFT and FIND functions to get the City name. And for State, we will combine the RIGHT, LEN, and FIND functions. This is because the City name is on the left side and the State on the right side.
📌 Steps:
- Go to Cell C5 of the City column.
- Put the following formula based on the LEFT and FIND functions.
=LEFT(B5,FIND(",",B5)-1)
- Press the Enter button.
We get the city name from our reference cell.
- Now, drag the Fill Handle icon downwards.
City names for all the cells are shown.
- We want to get the State names now. Go to Cell D5 and paste the following formula.
=RIGHT(B5,LEN(B5)-FIND(",",B5)-1)
- Again, press the Enter button and pull the Fill Handle icon
We get both the City and State names after applying the formula for cases. We can use the SEARCH function instead of the FIND function without any change in the result.
🔎 Formula Explanation:
- FIND(“,”,B5)
Search comma (,) on cell B5 and shows the position.
Result: 11
- FIND(“,”,B5)-1
Subtract 1 from the previous result.
Result: 10
- LEFT(B5,FIND(“,”,B5)-1)
It will show 10 characters from the left side.
Result: Montgomery
- LEN(B5)
It will show the length of Cell B5.
Result: 19
- LEN(B5)-FIND(“,”,B5)-1)
A subtract operation was performed.
Result: 7
- RIGHT(B5,LEN(B5)-SEARCH(“,”,B5)-1)
Show 7 characters from the right side.
Result: Alabama
Read More: How to Split Inconsistent Address in Excel (2 Effective Ways)
Conclusion
In this article, we discussed how to separate city and state in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
