How to separate city and state in Microsoft Excel

Separating city and state in Excel refers to the process of separating the city and state information that is combined in a single cell into two separate cells. This can be useful for data analysis, sorting, filtering, and generating reports.

In this tutorial, we will learn how to separate city and state in Microsoft Excel. In Microsoft Excel, the user can use multiple methods to separate a cell containing city and state into two different cells. We can utilize the Text to Columns Wizard, the TEXT SPLIT function, or the Flash fill feature.

Method 1: Using the Text to Columns Wizard 

Step 1 – Select the Cells and Locate the Data Tab

  • Select the range of cells containing the city and state to be separated.
  • Locate the Data tab in the menu bar.
  • Go to the Data tab.

Step 2 – Click on the Text to Columns Button

  • Click on the Text to Columns button in the Data Tools section of the Data tab.

Step 3 – Select the Delimited Option and Select the Delimiter

  • Select the Delimited option.
  • Select the Delimiters according to the data.

Step 4 – Enter the Destination

  • Enter the destination i.e. reference of the cell where you want to separate city and state.

Step 5 – Click on Finish

  • Click on the Finish option.
  • The city and state will be separated.

Method 2: Using the TEXTSPLIT Function

Step 1 – Select a Blank Cell and Place an Equals Sign

  • Select a blank cell where you want to separate the city and state.
  • Place an Equals sign in the blank cell.

Step 2 – Enter the TEXTSPLIT Function

  • Enter the TEXTSPLIT function right after the equals sign
  • The syntax of the TEXTSPLIT function is: 

                            TEXTSPLIT(A2, “ , ”)

  • The first argument i.e. A2 is the cell containing the city and the state.
  • The second argument i.e. “ , ” is the delimiter.

Step 3 – Press the Enter Key

  • Press the Enter key to separate.

Step 4 – Use Autofill Feature to Apply the Function on Each Cell

  • Use Autofill to apply the function on each cell.

Method 3: Using the Flashfill Feature

Step 1 – Separate the First Cell Manually

  • Separate the first cell containing the city and state manually.
  • This can be done by entering the city in one cell and the state name in the other.

Step 2 – Select the City and Press CTRL + E 

  • Select the cell in which you have entered the city.
  • Now press the CTRL + E keys.
  • This will instantly separate the city names.

Step 3 – Select the State and Press CTRL + E 

  • Select the cell in which you have entered the State.
  • Now press the CTRL + E keys.
  • This will instantly separate the State names.