All four elements of the employee addresses have been entered into a single cell in column G. While this may work well for some purposes, it isn't ideal. In this current state, the data can not be sorted, filtered, or otherwise analyzed by any of the individual elements. This means you could not use this data to see how many employees live in Indiana or to easily create mailing labels sorted by zip code.
Excel has an easy way to separate data into multiple columns.
To select the data in column G,
Click in cell G2, Press: Control key + Shift key + Down Arrow key
All of the data in column G is now selected.
The Text to Columns tool is located on the Data tab.
To move to the Data tab, on the Ribbon,
Click the Data tab
To open the Convert Text to Columns Wizard, in the Data Tools group,
The Convert Text to Columns Wizard opens:
The wizard begins by asking which type of data you have. In this case, the data is comma delimited. This means that each element of the data is separated by a comma.
To confirm that that data is delimited and move to the next step of the wizard,
The wizard moves to step 2. Here you are given the chance to determine which type of delimiter is used in your data. In this case, the data is comma delimited.
To deselect the Tab delimiter, if necessary,
Click the Tab checkbox
To select the Comma delimiter,
Click the Comma checkbox, Click
The third step of the wizard lets you type of format for each column. For now, don't make any changes to this.
To accept all of these changes,
The address data is separated into the three columns immediately to the right of the selected column. When this exercise file was created, columns for City, State, and Zip were included. It is important to note that if this had not been set up for you, the separated data would have overwritten whatever data was in these cells. Careful planning is required to ensure no data is lost.
Formatting for zip codes
Column J is labeled "Zip" and contains zip codes for cities around the United States. You may notice that some of these zip codes (cells J16, J23, J25, J29, J34, and J37) only have 4 digits. In reality these zip codes begin with zero (0). By default, Excel removes zeroes that are at the beginning of a number. So the zip code for Englewood, NJ changes from 07631 to 7631.
To remedy this issue, Excel has a format specifically for zip codes.
To select column J,
To open the Format Cells dialog box,
Right-click column J, Click Format Cells...
To see the options for Special formatting, in the Category area,
To format column J for zip codes, in the Category area,
Click Zip Code, Click
The zereos are returned to the zip codes.
To deselect column J,
Click any other cell
The following video demonstrates the process of splitting cell contents.