Naimul Hasan Arif
Who doesn’t love organized data? We love to have our data sorted. There is nothing wrong with it. We can decorate our dataset by separating city and state in different columns from the given addresses. In this article, we are gonna learn how to separate city and state in Excel without commas.
For more simplification, I am going to use a dataset containing Student ID, Name, and Location columns. I will separate city and state names in different columns given in the Location column.
Download Practice Workbook
3 Smart Ways to Separate City and State without Commas in Excel
1. Applying RIGHT, LEFT, FIND, and LEN Functions’ Combination
We can use a combined formula with RIGHT, LEFT, FIND, and LEN functions to separate city and state without commas. The whole procedure is described in the following section.
Steps:
- Add two columns named City and State in the dataset.
- Select a cell (i.e. E5).
- Now, input the following formula in that cell:
=LEFT(D5,FIND(" ",D5)-1)
- Next, press the ENTER button.
We will have the city name (i.e. Tampa) here.
- Use Fill Handle to AutoFill the rests.
- Followingly, go to cell F5.
- Input the formula mentioned below.
=RIGHT(D5,LEN(D5)-FIND(" ",D5))Formula Breakdown
LEN(D5) —> Returns the text length in cell D5.
Output: 13
FIND(” “,D5) —> Searches for a space in cell D5.
Output: 6
RIGHT(D5,LEN(D5)-FIND(” “,D5))
RIGHT(D5,13-6)
RIGHT(D5,7) —> It will return last seven letters.
Output: Florida
- Next, hit on ENTER.
- Now, AutoFill the rests.
Thus, we can separate city and state in Excel without commas.
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. Adopting Flash Fill Feature
Flash Fill is the simplest way to separate city and state in Excel without commas. No additional formula is needed here.
Steps:
- Write at least two city and state names in the City and State
- Next, select all the cells in the City column.
- Go to the Data tab.
- Then, select Flash Fill from the ribbon.
We will have the city names in the city column.
- Similarly, select all the cells in the State column.
- From the Data tab, click on Flash Fill.
We will have the state names in the State section.
Read More: How to Separate Address Number from Street Name in Excel (6 Ways)
3. Use of Text to Columns Command
Text to Columns command is another very cool way to separate city and state in Excel without commas.
Steps:
- Copy all the locations and paste them in the City column.
- Next, pick the Data tab.
- Click on Text to Columns from the ribbon.
Covert Text to Columns Wizard will appear.
- Choose Delimited – Characters such as commas or tabs separate each field
- Followingly, press Next.
- Then, check the Space option.
- Click on Next.
- Now, click on the Text option.
- Click on the Finish button.
Finally, click on OK from the warning box to complete the procedure.
Thus, we can have the sorted city and state in Excel without commas.
Read More: How to Split Inconsistent Address in Excel (2 Effective Ways)
Practice Section
You can try here for more expertise.
Conclusion
In this article, I have tried to explain 3 smart ways to separate city and state in Excel without commas. I hope it will be helpful for all. For any further questions, comment below. For more information regarding Excel, you can visit our Exceldemy site.
