One of the Advanced Excel techniques is Text to Columns. Excel’s “Text to Columns” feature is used for the following tasks:
- Splitting text of one cell in multiple columns (opposite of Concatenate)
- Changing the data format of a column (General, Text, Date)
- Converting numbers with trailing minus sign into negative numbers
- Converting invalid date formats to valid ones
- Importing data files such as CSV, TXT
- Bonus: Alternative to Text to Columns – FlashFill
Click here to Learn more advanced excel technique via Advanced Excel Training program
In essence, Text to Columns is used in data cleaning and data re-arrangement. We will take four examples to show how it works:
- Case Study 1: Split a name into two columns as last name and first name (Example: AbduSalaam, Ismael)
- Case Study 2: Transfer Single Columnar Data into Multiple Columns, and customizing the output cell format. (Example: Nos. with zero prefixes such as 000123 must be stored as text to retain the zeroes)
- Case Study 3: How to convert numbers with trailing minus as proper negative numbers. (Example: 200- to -200)
- Case Study 4: Unclean or Invalid Dates (DMY Format) should be converted to valid data formats. (Example: 22.10.2009 must be converted to a valid date format)
Case Study 1:
You have a long of list of full names as shown below (Example: AbduSalaam, Ismael). You need to split the text as last name and first name in two different columns.
You can apply Text to Column technique. It cuts the data into different columns. You need to specify the column delimiter i.e. the character where the data must be split. In the example given below, the delimiter is a comma, which exists between last name and first name.
In the above example, it is clear that full name is in a single column. It needs to be separated into two columns. One is the last name, and other is the first name.
1st Step: Choose the dataset and go to “Data” tab
2nd Step: Then click on Text to Columns
3rd Step: As you click on Text to Columns, you will get Text to Column Wizard box. The box indicates that you are in Step 1 of 3. In the first step, we will choose the Delimited option. Then click on Next button.
Step 4: In the next step, you need to switch on the Comma checkbox. As soon as you select comma checkbox, the single column appears to split into two columns (refer Data Preview box).
Step 5: Click on Next button to go to Step 3 of 3 of Text to Column Wizard. Here you need to choose the Destination cell. This will be the cell, wherein data after getting split will appear. In our example, the original list of names is in Column E., So I choose the very next of column’s adjoining cell. This way our original data remains intact. Then click on Finish.
By clicking on the finish, you will see all the surnames in one column, and the name is another column.
Case Study 2: Transfer Single Columnar Data into Multiple Columns.
Let’s see how to split the data into multiple columns. This is also part of data cleaning. Sometimes your data are in one single column, and you need to divide it into multiple adjacent columns for applying Sort, Filter or Pivot table.
All the information is in one single column, but you need to separate it. In our earlier example, we have applied “Delimited” technique. However, this time, we will apply “Fixed width” strategy of Text to Columns.
From the above data, you can understand that there are four pieces of information in a single cell i.e. Account No., Item No., Check No., and Description.
Our aim is to separate that one column in four different columns. Let’s see how it’s done:
Step 1: Select your data to range (from the first data cell). Go to Data tab, and then go to Text to Columns. On the “Convert Text to Columns Wizard – Step 1 of 3” box, choose Fixed Width option. Click Next.
Step 2: You will see the fixed width divider vertical line marks (called Break line) in the Data Preview window. You may need to adjust it as per your data structure.
- On double click, the brake line will be deleted
- When you click once, a new break line will be created at the point of click
- If you click an existing break line and drag it, it can be moved to the desired position
After placing appropriate break lines, click Next.
Step 3: As you click on next, you will reach Step 3 of 3 of Text to Columns wizard. You may change the destination cell so that your original data remains intact and output appears in adjoining columns’ cells.
If you click Finish, you will observe that the 3rd column of the output has last the prefix zeroes i.e. 00816530 gets converted to 816530, thereby corrupting the data.
Step 4: To retain the prefix zeroes, you should have chosen the column from the Data Preview window of Step 3 of 3 of Text to Columns wizard. It will blacken out the column as shown in the picture below.
Step 5: Once the column is blackened out, choose “Text” option from the list of options [General, Text, Date and Skip]. Now if you click on Finish, you will see the zeroes are retained in the final output columns.
Case Study 3: Converting numbers with trailing minus sign into negative numbers.
There are series of numbers, wherein some of the numbers are ending with minus sign (-). Now, if you want to apply sum, min, max or average functions, it will not work correctly as Excel cannot read 3789- as a negative.
Although, it is rare to see this kind of data set in today’s world, if you are working with old ERP systems or legacy MIS reports, then you will see such cases. Our objective is to convert the data of Column 1 into something that is shown under Column 2 as given below.
Let’s see how to achieve this using a hidden trick of Text to Column.
Step 1: Choose the data set (Column 1). Go to Data tab and then Click on Text to Columns. Then choose Delimited and press next. Uncheck all the boxes in Step 2 of 3 of Text to Columns wizard and then press next. Click Finish under Step 3 of 3 of Text to Columns wizard. You will see the final output with minus sign in proper place (leading or prefix).
Now your result for both the columns is same.
Case Study 4: Unclean or Invalid Dates (DMY Format) should be converted to valid data formats.
By default, Excel accepts date input in MM/DD/YY format (US format) unless you change the control panel settings of your PC. Example 22.10.2007 or 22/10/2007 date may be considered invalid.
Important Note: Technically, every valid date is a number to Excel.
Let’s see how Text to Columns can help clean date formats. Assume you have a recordset consisting of a large number of dates like below:
A lot of users select the entire range and then go to Format Cell and then change the format of the Date. But it will not change anything as it is not a valid date format in Excel since dot separators are invalid. If it is not a date, then it’s simple text to excel. Now how will you fix the issue?
From the above table, if you look at the 4th row then you can understand that it is 25th July. To make it a valid date you need to flip the position of 25 with 7 i.e. 7 should be first and then 25. Additionally, the separator should be a slash (/). Will you re-enter dates one by one manually? Very boring task!
Here’s what you need to do.
Step 1: Add an extra column (Remarks). Then ask a question to Excel if the date given in the first column is a number. You can use ISNUMBER formula. Since every valid date is a number, TRUE indicates, a date is a number and hence, possible a valid date.
Now after applying ISNUMBER formula, you are getting result either TRUE or FALSE. You don’t need to change the date where you are getting TRUE (read: valid dates).
Step 2: Sort the data table with respect to 3rd column (Remarks), so that TRUE and FALSE are sorted in a separate block.
Now this give will you true records on one side and false on another side.
Step 3: Now select only the dates against which the remarks are false.
Step 4: Go To Data tab and then Text to column. Choose Delimited option. Click on Next
Step 5: Ensure that none of the checkbox is switched ON in Step 2 of 3 of Text to Columns wizard. Click on Next.
Step 6: When you are in Step 3 of 3 of Text to Columns wizard, then please stop here for a moment. You have entered the wrong date in Excel, and you need to confess it to Excel. Where is the “confession box”? In Step 3, there is an option button called Date. Choose the format of the current status/sequence of date format. It is DMY (8.4.2007) in our case. Click on Finish.
Step 7: Excel will convert the invalid dates to valid ones. Isn’t that cool! This sort of date dumps is often seen in ERP reports of SAP, Oracle, Ramco, Tally etc. The advantage of having a clean date is that you can easily use Pivot Table’s Date grouping feature. Click here to know more about it.
Read this related article: Pivot Table Grouping Numbers & Dates
Text to Columns is a must know data cleaning technique. We have shown you advanced tricks of Text to Columns. It’s used to import data files such as CSV, TXT.
Bonus: An alternative to Text to Columns was introduced in Excel 2013 version. Check out how FlashFill feature can you clean data even faster.
Try our Free Online Excel Course – Update your skills of MS Excel