How to Separate City and State without Commas in Excel (3 Smart Ways)

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.

How to Separate City and State without Commas in Excel: 3 Smart Ways

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)

Formula Breakdown

FIND(” “,D5) —> Searches for a space in cell D5.
Output: 6

FIND(” “,D5)-1 —> Subtracts 1 from the previous result.
Output: 5

LEFT(D5,FIND(” “,D5)-1)
LEFT(D5,6) —> It will return the first five letters.

Output: Tampa

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.

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.

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.

Practice Section

You can try here for more expertise.

Download Practice Workbook

Separate City and State without Commas.xlsx

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.

<< Go Back to Address Format | Text Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!