Skillsheet-Using text-to-columns to split a column in Excel
Sometimes you will want to split the contents in one column in a spreadsheet into more than one column, perhaps to split a date into year, month and day, or an address into street number, street and city. The text-to-column function in Excel or OpenOffice allows you to split a column based on a character such as a comma, space, or dash. It treats the content in each cell as if it were a delimited text file, and splits it based on a delimiter you specify.
We’ll use data on the number of wedding licenses issued at City of Toronto civic centres and available on the city’s open data site. It comes as a CSV file and we’ve already imported it into a worksheet sheet in Microsoft Excel (Mac 2016 version shown).
It’s a nice simple dataset, but what if we wanted to know the total number of permits by month, or by year? We could do it without altering the table by using string functions, but it would be easier to simply split the column from the get go. Fortunately, we can use Text to Columns to divide the month and years into separate columns. Let’s do that now.
To begin, add a new column where column B is now by highlighting column B (click on the “B” label at the top) and choosing Insert>Insert Sheet Columns on the Excel home ribbon.
A new column will appear and the remaining data will be shifted one column to the right.
We needed to add a column because when we split the year and the month, the month will need to go in its own column and without a blank one, the new split-off column will overwrite data to the right.
Now, highlight column A, the one you want to split, ahd click on the Text to Columns icon on the Data ribbon, shown to the right of this image.
That will bring up this “wizard” dialogue.
We intend to split the data based on a delimiter, so we’ll choose the top radio button. The Fixed Width radio button allows you to split the data at points specified by set numbers of characters from the left.
Clicking next takes you to the next step of the wizard.
In this case, we know that the year and month are divided by a dash in column A, so we will choose Other as the delimiter and type in a dash.
Click Next to see the wizard’s last step.
Here, we can set the data format for each column. We’re going to leave both as General, the catch-all data format in Excel. We could also choose to make one or both columns plain text, or if it was a date column, apply a date format.
That’s it. Click finish to see the one column become two. In this image, we have already changed the column headers to Year and Month.
Now we have separate year and month columns that we can sort and filter separately, or use in a pivot table analysis.
Other spreadsheet programs such as OpenOffice can do this as well. Google sheets have the handy split() function to do the same thing. As we noted before, you can also use string functions in Excel to do more sophisticated splits.
Modified: April 5, 2016, 10:45 am AST