Many times on a project you have the right data, but in the wrong format. I’ve talked before about how you can use Excel to combine multiple columns of data into one. Today I’ll show you how you break apart a single column of data into two or more columns using Text To Columns.
Let’s say you have an address list in Excel and there is one column for the name with the names formatted as last name, first name. But you want first name and last name in separate columns. Select the column that the names are in, click on the Data tab on the ribbon, and click the Text To Columns button.
The Convert Text to Columns Wizard will open. The first step asks if your data is delimited or fixed width. Delimited means there is a specific character, such as a comma, tab, or space that separates each piece of information. Fixed Width means that a certain number of characters is allotted to each piece of information. Since we know that there will be a space separating the first name and last name, choose Delimited and click Next.
In the next window, uncheck Tab and check Space in the list of delimiters (field separators). This tells Excel that anything separated by spaces should be split into different columns. You will see vertical lines appear in the preview of your data to show where your data will be broken into columns. If you had chosen fixed width instead of delimited you could click in the preview of your data to insert column breaks. The text qualifier allows you to select a character that tells Excel to ignore any occurrences of your delimiter character that fall between two text qualifiers. In other words, if your delimiter is a space and your text qualifier is a quotation mark, Excel knows that any spaces that fall between two quotation marks are part of your data, and do not indicate a new column break. For example Ludwig van Beethoven would get split into 3 columns, but Ludwig “van Beethoven” would only be split into 2 columns because the space between the quotation marks would not be considered a delimiter. Click Next.
In the third step you can format each column as General, Text, Date, or tell Excel to skip that column. In most cases you can leave it on General and Excel can figure out what is text and what is a number or date. However if you your data has numbers with leading zeros, such as zip codes, be sure to set that column as Text. Since numbers can’t have zero as their first digit, Excel will remove any leading zeros from numbers if you leave the format set to General.
If you want the new data to be located somewhere other than the original column and the columns to its right, you can specify the new location in the Destination field. Click Finish to return to the worksheet and see your new data.
When Excel splits your data into columns, it does not insert new columns to hold that data. If anything is in the cells to the right, it will be overwritten by the data from the cells you are splitting. So It’s always a good idea to insert a few extra columns before using Text To Columns.
