• Home  / 
  • Excel
  •  /  Tricks of Text to Columns in MS Excel

Tricks of Text to Columns in MS Excel

Tricks of Text to Columns in MS Excel

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:

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.

Solution:

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.

Split the Data in to two Column with help of Text to Column

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

Go to 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.

choose the Delimited option - Convert text to columns wizard step 1 of 3

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).

select comma checkbox - Convert Text to Columns Wizard

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.
choose the Destination cell while using Convert Text to Columns wizard

Result:

By clicking on the finish, you will see all the surnames in one column, and the name is another column.

Solution of Text to Columns

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.

Transfer Single Columnar Data into Multiple Columns using Text to Columns
Observation:

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.

Convert Text to Columns Wizard – Step 1 of 3 box, choose Fixed Width option.

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

You will see the fixed width divider vertical line marks you may need to adjust it as per your data structure - Convert Text to Columns wizard

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.

 

change the destination cell - Convert Text to Columns Wizard
Important Note:

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.

Select particular column and Select Number stored as Text while using Text to ColumnsStep 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.

Solution with the help of Text to 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.

Converting numbers with trailing minus sign in to negative numbers while using Text to Columns

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).

final output with minus sign in proper place with help of Text to Columns

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:

in case of Invalid Dates should be converted in valid data formats use text to columns in excel
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?

Learn about Excel Date Functions

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.

use ISNUMBER formula

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.

Sort the Data (Remarks)

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.

tick date checkbox and will select DMY from the dropdown box in our case while using text to columns in excel

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

Conclusion:

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

 

About the author

Rishabh Pugalia and Niladri Sekhar Biswas

Author CA. Rishabh Pugalia has 10+ yrs. of work experience in Audit, Interest Rates Trading & I-Banking & Author Niladri Sekhar Biswas has 9 years of experience working in Microsoft Excel, as Excel and VBA corporate trainer

  • Informative tutorials about the split text function. This post is very useful for beginners. Thank you for sharing. Keep it up.

  • AXbeantech

    Great tutorial with graphics about the split text function. It’s one of the least used, but most useful functions out there, especially for imported data. One of the shortcuts I recommend for splitting simple data into columns is QuikBots for Excel. For beginners, it shortens the number of steps to get things done.

  • seo

    Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword….wait there’s even more Now what if i told you there was a simple WordPress plugin that does all the On-Page SEO, and automatically for you? That’s right AUTOMATICALLY, just watch this 4minute video for more information at. Seo Plugin

  • Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword….wait there’s even more Now what if i told you there was a simple WordPress plugin that does all the On-Page SEO, and automatically for you? That’s right AUTOMATICALLY, just watch this 4minute video for more information at. Seo Plugin

  • sathyanarayanan

    very useful

  • Shubhendu Bose

    This is awesome…….. and well explained.

    Thanks Niladri….

  • Sai

    Very Helpful – Uncleaned Dates stuff

  • Raju

    So well explained!! Thanks Niladi