How to Separate City and State in Excel (3 Effective Methods)

Usually, an address consists of house number, street, city and state names, zip, etc. Typically, they are segmented by a symbol like Comma(,). For this article, we think of part an address which consists of city and state name. We want to separate the city and state in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Methods to Separate City and State in Excel

We will discuss 3 methods to separate city and state in Excel. Our dataset includes the name of cities and states.


1. Use Excel Text to Columns Wizard

The Text to Columns is a useful feature of Excel. This feature separates a group of the text string from a cell into multiple cells based on a delimiter.

In this section, we will use this Text to Columns tool to separate city and state.

📌 Steps:

  • We added two columns named City and State to separate the address into them.

  • Now, copy the data from the Address column using Ctrl+C. After that, paste them into the City column by pressing Ctrl+V.

  • Select all the cells of the City column.
  • Press the Data tab.
  • Choose Text to Columns from the Data Tools group.

Excel Text to Columns to Separate City and State

  • The Convert Text to Columns Wizard window appears.
  • Check the Delimited option and then click on the Next button.

Excel Text to Columns to Separate City and State

  • 2nd window of Convert Text to Columns Wizard appears now.
  • Check the Comma and Space options.
  • Again, click on the Next button.

Excel Text to Columns to Separate City and State

  • Lastly, 3rd window appears.
  • Choose the Text option.
  • Finally, hit the Finish button.

Excel Text to Columns to Separate City and State

  • A warning window appears. Press OK there.

  • Look at the dataset.

City and State names are separated successfully.

Read More: How to Format Addresses in Excel (4 Easy Methods)


Similar Readings


2. Apply Flash Fill Feature     

The Flash Fill is a built-in feature of Excel. It fills a column or rows automatically following a pattern given by the user. It is available in Excel 2013 and later versions.

In this section, we will use this Flash Fill feature to separate addresses. We will show both ribbon and keyboard shortcuts for this method.

📌 Steps:

  • First, we need to manually input city and state information on the City and State columns respectively.
  • We insert information on the first two cells of both columns.

  • Now, select all the cells of the City column.
  • Go to the Data tab.
  • Choose Flash Fill from the Data Tools group.

Flash Fill to separate city and state

  • Look at the dataset now.

The City column is filled with data. Now, we will show the keyboard shortcut.

  • Similarly, we select the cells of the State column.
  • Then, press the Ctrl+E.

Flash Fill to separate city and state

  • Again, look at the dataset.

The State column is also filled with desired data.

Read More: How to Separate Address Number from Street Name in Excel (6 Ways)


3. Combine RIGHT, LEFT, FIND, and LEN Functions to Separate City and State

In this section, we will use the combination of the LEFT and FIND functions to get the City name. And for State, we will combine the RIGHT, LEN, and FIND functions. This is because the City name is on the left side and the State on the right side.

📌 Steps:

  • Go to Cell C5 of the City column.
  • Put the following formula based on the LEFT and FIND functions.
=LEFT(B5,FIND(",",B5)-1)

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

  • Press the Enter button.

We get the city name from our reference cell.

  • Now, drag the Fill Handle icon downwards.

City names for all the cells are shown.

  • We want to get the State names now. Go to Cell D5 and paste the following formula.
=RIGHT(B5,LEN(B5)-FIND(",",B5)-1)

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

  • Again, press the Enter button and pull the Fill Handle icon

We get both the City and State names after applying the formula for cases. We can use the SEARCH function instead of the FIND function without any change in the result.

🔎 Formula Explanation:

  • FIND(“,”,B5)

Search comma (,) on cell B5 and shows the position.

Result: 11

  • FIND(“,”,B5)-1

Subtract 1 from the previous result.

Result: 10

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

It will show 10 characters from the left side.

Result: Montgomery

  • LEN(B5)

It will show the length of Cell B5.

Result: 19

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

A subtract operation was performed.

Result: 7

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

Show 7 characters from the right side.

Result: Alabama

Read More: How to Split Inconsistent Address in Excel (2 Effective Ways)


Conclusion

In this article, we discussed how to separate city and state in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo