fbpx
Yodalearning>Tutorials>Text to Columns in Excel | Split to Columns

Do you know what Text to Columns is feeling right now? It is feeling sad and unappreciated. It is because it gets less credit than it actually deserves. Text to Columns in Excel is one of the most wonderful features in Excel.

The beauty of Text to Columns will help you to Split Text into desired no. of Columns.

Here in this tutorial, you’ll learn about the various things you can do with Text to Columns in Excel. As its name suggests, it helps to split the text into many columns. Let’s take an example, if you have a first name and last name in the same cell, what Text to Columns can do? It will help you to split these into two different cells.

In this blog, you’ll learn:

  1. What is Text to Columns in Excel?
  2. How Text to Columns can Split Email Ids into Username and Domain Name
  3. How Text to Columns can Get the Root Domain from URL
  4. How Text to Columns can Convert Text to Numbers
  5. How Text to Columns can Extract First five Characters of a String

Text to Columns is a feature in Excel. It will help you to Split your Text into desired no. of Columns. Given below is an image. Here, we have split the University Name and its City into separate Columns.

what is Text to Column

Text to Columns can Split Email Ids into Username and Domain Name:

With Text to Columns, you can select your own delimiter to split text.

Text to Columns in Excel will help you to split the emails addresses into usernames and domain names. Also, for the domain names is recognized with the @ sign.

Imagine you have a Dataset where you’ve few fictional email ids of your choice and you want to split the Usernames and the Domain Names.

In our case, we have split the email address into the Name and Domain:

Split Email Ids into Name and Domain

How will you do this??????

You can follow these simple steps to split the email addresses into the Usernames and the Domain Names with the help of the Text to Columns Feature:

Steps to split email address into username and domain names:

  • Select the data set
  • Go to Data> Data Tools
  • Go to Text to Columns (Convert Text to Columns Wizard will open)
  • Select/Tick Delimited (this is the default selection)
  • Then Click on Next
  • In the Delimiters Option select Other (In front of Other enter “@”)
  • Note: Make sure to uncheck all the options other than “Other” (if checked)
  • Then Click on Next
  • Change the destination cell to the one where you want the result. In our case: Destination: $B$2
  • Click on Finish

By doing this you’ll be able to see the email address, the Username and the Domain name in separate Columns. This is how Text to Column helps to split Text into Columns.

Text to Columns can Get the Root Domain from URL

Do you often work with web URLs? Whether you work with Root Domains or not, you must know how to find or trace the total no. of Unique root Domains.

For example, in our case:  https://yodalearning.com/courses and https://yodalearning.com/tutorials

The main root domain is https://yodalearning.com

Suppose you have a dataset as shown below:

Follow these simple steps to get the root domain from these URLs:

Steps to get root domain from URLs:

  • Select the data set
  • Go to Data> Data Tools
  • Go to Text to Columns (Convert Text to Columns Wizard will open)
  • Select/Tick Delimited (this is the default selection)
  • Then Click on Next
  • In the Delimiters Option select Other (In front of Other enter “/”)
    Note: Make sure to uncheck all the options other than “Other” (if checked)
  • Then Click on Next
  • Change the destination cell to the one where you want the result. In our case: Destination: $B$2
  • Click on Finish

Root URL domain (1)

Root URL domain (2 & 3)

Important Tip: Make sure that all the URLs have http:// in the beginning, or else you’ll obtain the Root Domain in the First Column itself.  Ensure the Consistency of the URLs before getting started with Text to Columns.

Text to Columns can Convert Text to Numbers

There are times when numbers get Converted into Text while importing the Data from the Database.

Do you know why the Numbers get Converted into Text?

There are a Couple of Reasons responsible for Converting Numbers into Text:

  1. If an Apostrophe has been put before the number. Here, the Number will convert into Text
  2. Numbers from the Text Functions like LEFT, RIGHT, or MID

Now, you might be thinking what’s the complication here? The problem with the numbers in Text Format is, they are not recognized by Excel Functions such as SUM and AVERAGE.

If you’ve got a Dataset with numbers in Text Format as shown in the image below:

Covert text number to Number

You’ll need to follow these simple steps to convert text to numbers:

Steps to convert text to numbers:

  • Select the data set
  • Go to Data> Data Tools
  • Go to Text to Columns (Convert Text to Columns Wizard will open)
  • Select/Tick Delimited (this is the default selection)
  • Then Click on Next
  • In the Delimiters Option, make sure all the options are unchecked
  • Then Click on Next
  • Go to General in the Column Data Format. Make changes in the destination cell. In our case: Destination: $B$2
  • Click on Finish

Steps to Convert Text number to Number (1)

Steps to Convert Text number to Number (2 & 3)

Wow! Text to Column has Converted the Numbers back into the General Format. And these numbers are good to use in the Formulas.

Text to Columns can Extract First five Characters of a String

There are times when you want to extract only certain characters from a string. This could be the case when you’ve Transactional Data or the Data which represents a Unique identifier. It is the same when you see a Bar Code.

Imagine a situation, where you wish to extract the first few characters of a string. It could be the case where you have the Pan Card no. and the first five characters (or any other number of characters) these nos. and characters must represent a unique identifier.

For example, in the data set shown below, the first five characters extracted are unique to the PAN no. of people. You can see the same in the image given below:

You’ll need to follow these simple steps to extract the First five Characters from the Dataset using Text to Columns.

Steps to extract the first five character from the dataset:

  • Select the data set
  • Go to Data> Data Tools
  • Go to Text to Columns (Convert Text to Columns Wizard will open)
  • Select/Tick Fixed Width (this is not a default selection)
  • Then Click on Next
  • In the Data preview section, drag the vertical line and place it after five characters in the text.
  • Then Click on Next
  • Go to General in the Column Data Format. Make changes in the destination cell. In our case: Destination: $B$2
  • Click on Finish

Steps to Split First 5 No. (1)

Steps to Split First 5 No. (2 & 3)

 

Brilliant! In front of you lies a Data that has been split into the first five characters from each transaction id. The first five characters would be in the first Column and the rest in the Second.

Note: It is up to you to decide the no. of Columns before you split your data. You’ll have to set more than one Vertical line to do so.

To do so:

  1. Click anywhere in the Data Preview area
  2. Drag the cursor to set the divider

Excel Text to Columns or Split Cells in Excel is used in data cleaning and data re-arrangement. Excel text to columns formula technique is used for following:

How to Split Text into Multiple Column?

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:

You can apply Text to Column technique or split cell technique. It cuts the data into different columns. 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. Separate into two columns. One is the last name, and other is the first name.

8 Excel Challenges to Know How good you are in excel?  Accept the Challenge

Before

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

After

Solution of Text to Columns

Recommended Courses

Learn how to do text to column or split text in multiple column or you can learn such more tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

Testimonial by spreadsheet guru

Text to Columns

To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. For example, when you want to separate a list of full names into last and first names.

To separate fixed-width text into multiple columns, follow these steps:

  1. Select Range of Cells
  2. Choose the dataset and go to “Data” tab
  3. Click on Text to Columns

    Go-to-Data-Text-to-Columns
  4. Choose the Delimited option.
  5. Click On Next button.
  6. Click on Comma button then next button.
  7. Choose Destination cell
  8. 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. This is the process of Text to columns in Excel

Solution of Text to Columns

We have solved another interesting query of excel users, in case if you wish to convert an invalid date format into a valid date format.

Learn Remove Password From Excel and Using Excel VBA (Coding) or you can learn such more tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

You can also use text to a column in excel VBA, to get Excel VBA tutorial you need to enroll in our course

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.

Now you can convert or fix digits with trailing minus into negative digits. Check out how by clicking here:

Our aim is to separate that one column in four different columns. Let’s see how its done:

Step 1: 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-

We have solved another interesting query of quite a few people, in case if you wish to convert an invalid date format into a valid date format. Click here:

3 Responses
  1. Bhakti

    It really helped me to split names in two different cells. Got to know how to use text to column function in Excel.
    Thanks Yodalearning.

Leave a Reply

Yoda learning Group

Related Tutorials

The Ultimate Guide to Excel Pivot Table Slicers
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
Insert Symbol Dialog Box to Insert or Type degree symbol in Excel
May 7, 2018
CHAR Formula to Insert or Type degree symbol in Excel
May 7, 2018
How to type degree symbol in Excel using Keyboard Shortcut
May 7, 2018
HOW TO FREEZE ROWS AND COLUMN IN EXCEL?
May 4, 2018
What are Reference Styles? How to Use R1C1 Reference Style in Excel
May 3, 2018
How to use the Histogram Tool in Excel (Step By Step Guide)
April 24, 2018
What is Flash Fill ? How to use flash fill in Excel ?
April 23, 2018
How TO TRACK CHANGES in MS Word
April 23, 2018
Undo – Redo Shortcuts Keys in Excel
April 18, 2018
How To Remove Blank Rows – The COUNTBLANK Function
April 9, 2018
How to match names in Excel where spelling differ or vary
April 9, 2018
Learn How to enable or disable scroll lock in Excel
April 7, 2018