Text to Columns in Excel or Split Cells in Excel is used in data cleaning and data re-arrangement. Excel text to columns formula technique is used for following:
- 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.
We will take four examples to show how text to column 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)
How To Use Text To Columns In Excel
Case Study #1: Split the text as last name and first name in two different columns.
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 given 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.
Below are the Steps on how to do text to column :
- Choose the dataset and go to “Data” tab
- Then click on Text to Columns
- Choose the Delimited option. Click On Next button.
- Click on Comma button then next button.
- Choose Destination cell and Finish.
Result: By clicking on the finish, you will see all the surnames in one column, and the name is another column.
Also, How to Split Cells in Excel
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 its done:
Step 1 for text to column: Select your data to the 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:
Now look at the very first date – Is it 8th April or 4th August? Confusing, right?
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 a Delimited option. Click on Next
Step 5. Ensure that none of the checkbox is Switched ON in Step 2 of 3. 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, 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.
Text to Columns is a must know data cleaning technique. We have shown you advanced tricks of Text to Columns. Use can use 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. excel split cell into multiple rows