How to Separate City and State in Excel – 3 Methods

Posted on by Alok Paul

The dataset includes the name of cities and states.

Method 1 – Using the Excel Text to Columns Wizard

Steps:

  • Add two columns: City and State.

  • Copy the data in the Address column pressing Ctrl+C. Paste it into the City column pressing Ctrl+V.

  • Select the City column.
  • Go to the Data tab.
  • Choose Text to Columns in Data Tools.

Excel Text to Columns to Separate City and State

  • The Convert Text to Columns Wizard window will be displayed.
  • Check Delimited and click Next.

Excel Text to Columns to Separate City and State

  • In the 2nd window of Convert Text to Columns Wizard, check Comma and Space.
  • Click Next.

Excel Text to Columns to Separate City and State

  • In the 3rd window, choose Text.
  • Click Finish.

Excel Text to Columns to Separate City and State

  • In the warning window, click OK.

This is the output.

City and State names are separated.

Read More: How to Separate City and State without Commas in Excel

Method 2 – Applying the Flash Fill Feature     

 Steps:

  • Enter city and state manually in the first two cells columns C and D..

  • Select all the cells in the City column.
  • Go to the Data tab.
  • Choose Flash Fill in Data Tools.

Flash Fill to separate city and state

This is the output.

  • Select the cells in the State column.
  • Press Ctrl+E.

Flash Fill to separate city and state

This is the output.

 

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

Method 3 – Combining the RIGHT, LEFT, FIND, and LEN Functions to Separate City and State

Steps:

  • Go to C5 in the City column.
  • Use the following formula:
=LEFT(B5,FIND(",",B5)-1)

Combine RIGHT, LEFT, FIND, and LEN Functions to separate city and state

  • Press Enter.

The city name is displayed.

  • Drag down the Fill Handle to see the result in the rest of the cells.

  • To get the State names, go to D5 and enter the following formula.
=RIGHT(B5,LEN(B5)-FIND(",",B5)-1)

Combine RIGHT, LEFT, FIND, and LEN Functions to separate city and state

  • Press Enter button and drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

 Formula Breakdown

FIND(“,”,B5)

Searches comma (,) in B5 and shows the position.

Result: 11

FIND(“,”,B5)-1

Subtracts 1 from the previous result.

Result: 10

LEFT(B5,FIND(“,”,B5)-1)

shows 10 characters from the left side.

Result: Montgomery

LEN(B5)

shows the length of B5.

Result: 19

LEN(B5)-FIND(“,”,B5)-1)

performs a subtraction.

Result: 7

RIGHT(B5,LEN(B5)-SEARCH(“,”,B5)-1)

Shows 7 characters from the right side.

Result: Alabama

Read More: How to Split Inconsistent Address in Excel

Download Practice Workbook

Download the practice workbook.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!

Leave a Reply

Your email address will not be published. Required fields are marked *

Advanced Excel Exercises with Solutions PDF