7. Split Address Field into Multiple Columns
The address field often contains some useful geographic data such as state and postcode. As a result, it always makes sense to separate this data into individual columns for sorting and filtering purposes.
How to split a column
ALT + A, E: Access Text-to-Columns wizard
1. Select Delimited if information separated by comma
2. Select the correct delimiter, check preview and click Next
3. Select correct number format for each column
TRIM: Remove whitespace before and after text
CTRL + SHIFT + →: Select all cells within data region
ALT + E, S, V: Paste values
CTRL + A: Select data region
ALT + A, T: Add filters
The address field in a data set often contains very helpful information such as city, state code and post code, which when separated, can be used to perform useful sorting and filtering by location.
Instead of writing complex formulas to chop these strings from the address field, instead we can use a tool called the TEXT TO COLUMN wizard located in the Data tab.
Our first step will be to select all of the addresses, so I'll do that with Ctrl + Shift + down arrow and then I'll open the TEXT TO COLUMNS wizard with the shortcut Alt + A E.
If the split in the text string is represented by a character such as a comma or a tab then we select the Delimited option and then press Next.
On the next screen we select the correct delimiter which is going to be Comma and I'll remove Tab and then we'll click Next again.
And in the final screen we'll decide what column data format we'd like to have for our output, and I'll leave it at General for now.
So I'll then click Finish to complete the split.
And you can see the split has been implemented correctly using the TEXT TO COLUMNS wizard.
But we still have some work to do.
First of all, there is some white space before the city and the state and post code columns.
To remove white space before and after a string we use a very useful function called TRIM.
So I'll move to the right and perform a TRIM calculation here.
So I'll write equals TRIM, open a bracket and then select the text field that I'd like to trim.
I'll then close the bracket and press Enter.
And now you can see that the white space has been removed.
I'll copy and paste this for the next cell, so that it applies to the state and post code and then I'll autofill for the remaining values.
And then Alt + O C A to auto fit the column width.
I'd now like to delete the old columns which still have the white space but unfortunately they're the input to our new formula, so I'll have to copy and paste values in the selected cells before doing so.
So I'll copy with Ctrl + C and Alt + E S V to paste values.
And now the formula has been removed and I can delete the old cells.
Our last task will be to separate the state code and the post code and the TEXT TO COLUMNS wizard can be used again to do this.
So I'll select the cells with Ctrl + Shift + down arrow and Alt + A E to bring up the TEXT TO COLUMNS wizard.
Delimited is the correct option again, but this time the delimiter will be a Space rather than a Comma.
And we can see in the data preview that the split is working correctly.
On the final page I'll switch the Column data format type to Text for our postcodes, because I don't want Excel interpreting our postcodes as numeric values.
I'll then click Finish and you can see the split has been implemented correctly using the TEXT TO COLUMNS wizard.
To finish off this lesson I'll just change the column headings in the top row.
Now if we select our data set with Ctrl + A and then implement a filter with Alt + A T, we can scroll to the end and create a filter in the state column which only includes the Californian addresses.
And now I've isolated all of my users based in that state.
When you look at a data set for the first time it's always worth asking, what specific data will I need for sorting and filtering this data set? Once that information has been decided, you can then use Excel’s text functions such as LEFT, RIGHT, TRIM and the TEXT TO COLUMNS wizard to isolate that data correctly.
Starting with the next lesson, the remainder of this course will focus on manipulating date and time values.
Hope you can join me for it.