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


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)

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 Format Addresses in Excel (4 Easy Methods)


Similar Readings


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

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 (2 Effective Ways)


Practice Section

You can try here for more expertise.

How to Separate City and State in Excel without Commas


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.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo