Yodalearning>Tutorials>Text to Columns in Excel | Split cells in excel

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:

While covering up this blog, we have explained how to split Text into multiple columns easily. This blog is basically for the people who face problems in Excel because they do not know the basic Excel Techniques.

Well, splitting Text into multiple columns is easy. There are people who wonder how to split data cell in two different rows or how to transfer or use the data given in a particular row by transferring in various other columns.

You just need to understand the toolbars and the place from where you want to separate the complex pair of words along with their punctuations into different other columns.  Splitting text cells is usually done for data cleaning and data re-arrangement. And, also for:

Well, splitting Text into multiple columns is easy.

You just need to understand the toolbars and the place from where you want to separate the complex pair of words along with their punctuations into different other columns.  Splitting text cells is usually done for data cleaning and data re-arrangement. And, also for:

  • Splitting text of one cell into 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.

If a cell has got more than just a word for e.g.: First name and surname i.e. the last name. Then, select the cell, range, and the cell containing the text values that you wish to split. You have a long of list of full names as shown below (Example: AbduSalaam, Ismael). You need to split the text into last name and first name in two different columns.

Solution:

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

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 :

  1. Choose the dataset and go to “Data” tab
  2. Then click on Text to Columns
    text-to-column-excel-online
  3. Choose the Delimited option. Click On Next button.
  4.  Click on Comma button then next button.how to use text to column in excel
  5. Choose Destination cell and Finish.

Text-to-Columns-Wizard-Step-3

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


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.

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

Transfer Single Columnar Data into Multiple Columns using Text to Columns

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.

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 Columns

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.

Solution-of-multiple-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).

Converting numbers with trailing minus sign in to negative numbers while using 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?

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

Separate data in single column

Step 7: MS Excel will convert the invalid dates into the valid ones. Isn’t it really cool? These sort of date dumps are often seen in ERP reports of SAP, Oracle, Ramco, Tally etc. There are advantages of having a clean date and one such advantage is that you can easily use Pivot Table’s Date grouping feature. Click here to know more about it.

Conclusion:

There are times when there is a lot of information in one text cell and one cannot view all the text in one go. In such cases, splitting the Texts into Columns is a must to know data cleaning technique. This technique basically brings clarity to the text. We have shown you advanced tricks of Text to Columns. This technique can also be used to import data files such as CSV, TXT.

Bonus: An alternative to Text to Columns was introduced in Excel 2013 version. You can check out how FlashFill feature can be used to clean the data even faster.

Try our Free Online Excel Course – Update your skills of MS Excel. excel split cell into multiple rows

 

Leave a Reply

Advanced Excel Course

Your courses were useful for me. Thank you again for your quick support and also for your availability.

- Madalina Elena Gheorghe

Free Excel Course
with 210+ Tricks
(UPDATED 2017)