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

Get FREE Advanced Excel Exercises with Solutions!

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


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

How to Separate City and State in Excel without Commas

  • Next, press the ENTER button.

We will have the city name (i.e. Tampa) here.

How to Separate City and State in Excel without Commas

  • 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.

How to Separate City and State in Excel without Commas

  • Now, AutoFill the rests.

Thus, we can separate city and state in Excel without commas.

Read More: How to Separate Address in Excel with Comma 


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

How to Separate City and State in Excel without Commas

  • 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.

How to Separate City and State in Excel without Commas

  • 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.

How to Separate City and State in Excel without Commas

Read More: How to Separate Address Number from Street Name in Excel 


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.

How to Separate City and State in Excel without Commas

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.

How to Separate City and State in Excel without Commas

  • Now, click on the Text option.
  • Click on the Finish button.

Finally, click on OK from the warning box to complete the procedure.

How to Separate City and State in Excel without Commas

Thus, we can have the sorted city and state in Excel without commas.

Read More: How to Split Inconsistent Address in Excel 


Practice Section

You can try here for more expertise.

How to Separate City and State in Excel without Commas


Download Practice Workbook


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo