Excel’s text-to-columns feature divides text in a cell into multiple columns. This simple task can save user the trouble of manually splitting text in a cell into multiple columns.
We’ll start with a simple example of splitting two samples of data into separate columns. Next, we’ll examine two other uses for this function that are not known to most Excel users.
Text in delimited text columns
For the first example, we’ll use the “Text to Columns” model with comma-delimited data. This is the most common scenario for text splitting, so let’s start with this one.
In the example data below, we have a list of names in a column. We want to separate first name and last name in different columns.
In this example, we want the first name to stay in column A, so the last name moves to column B. We already have some information in column B (the department). So first we need to insert a column and give it a title.
Next, select the range of cells that contains the names, and then click Data > Text to Columns
This will open a wizard where you will complete three steps. The first step is to determine how the content will be separated. Delimited means that the different pieces of text you want to separate are separated by a special character such as a space, comma, or slash. This is the one we will select here. (We’ll talk about the fixed-width option in the next section).
In the second step, specify the delimiter. In our simple data example, first and last name are separated by a space. So we uncheck the “Tab” option and add a check mark to the “Space” option.
In the last step we can format the content. In our example, we don’t need to apply any formatting, but you can, for example, specify whether the data is in text format or date format, and even choose to convert one format to another.
We also leave the target at $A$2, so it detaches the name from its current position and moves the last name to column B.
When we click “Finish” in the wizard, Excel separates first name and last name and we now have our new column B completely filled out.
Text in fixed-width columns
In this example, we’re splitting fixed-width text. In the data below we have an invoice code that always starts with two letters followed by a variable number of digits. The two-letter code represents the customer and the numeric value that follows represents the invoice number. We want to separate the first two characters of the invoice code from the numbers that follow it, and put those values in the Customer and Invoice Number columns we created (columns B and C). We also want to keep the full invoice code in column A.
Since the invoice code always has two digits, it has a fixed width.
First, select the range of cells that contains the text you want to split, and then click Data > Text to Columns.
On the first page of the wizard, select Fixed Width, then click Next.
On the next page we need to specify the position(s) in the column to share the content. We can do this by clicking in the preview area provided for this purpose.
note : The Text to Columns feature sometimes provides one or more breaks. It may save you some time, but keep an eye on it. Suggestions are not always correct.
In the Preview Data area, click where you want to insert the pause, and then click Next.
In the last step, enter cell B2 (=$B$2) in the “Target” field and then click “Finish”.
The invoice numbers are successfully separated into columns B and C. The original data remains in column A.
So we considered dividing the content with fixed delimiters and widths. We also explored the possibility of splitting text in place and distributing it in different places on a worksheet. Now let’s look at two other special uses of column text.
Conversion of American data to European format
A fantastic use of text in columns is to convert date formats. For example, converting an American date format to a European format or vice versa.
I live in UK. When I import data into an Excel spreadsheet, sometimes it is saved as text. This is because the source data is from the United States and the date formats do not match the regional settings configured in my installation of Excel.
So its text in columns to the rescue to convert them. Below is some US format data that my Excel copy didn’t understand.
We first select the range of cells that contains the data to convert, then click Data > Text to Columns.
On the first page of the wizard we leave the content as delimiter and in the second step we remove all delimiter options as we don’t really want to split the content.
On the last page, select the Date option and use the list to specify the date format of the received data. In this example, I choose MDY, which is the format commonly used in the United States.
After clicking on “Finish”, the data is successfully converted and is ready for further analysis.
Conversion of international number formats
Text to Columns is not only a tool for converting various date formats, but it can also convert international number formats.
Here in the UK, a decimal point is used in number formats. For example, the number 1064.34 is just over a thousand.
But in many countries, a decimal point is used instead. This number would therefore be misinterpreted by Excel and stored as text. The number would then be represented as 1064.34.
Fortunately, when working with international number formats in Excel, our good friend Text to Columns can help us convert these values.
In the example below I have a list of numbers formatted with a decimal point. So my locale in Excel didn’t recognize them.
This process is almost identical to what we used for the date conversion. Select the range of values, go to Data > Text to Columns, select the Delimited option and remove all delimiters. In the last step of the wizard, this time we select the “General” option and then click on the “Advanced” button.
In the settings window that opens, enter the desired character in the thousands separator and decimal separator fields provided. Click OK and then click Finish when you return to the wizard.
Values are converted and are now recognized as numbers for further calculations and analysis.
The Text to Columns method is more powerful than people think. Its classic use of splitting content into different columns is incredibly useful. Especially when working with data we receive from other people. The lesser-known functions for converting international date and number formats are magic.