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.

Here in this tutorial, you’ll learn about the various things you can do with Text to Columns. As its name suggests, it helps to split the text into many columns. It is not an text to column formula, it’s an wizard which works on click. Let’s take an example, if you have a first name and last name in the same cell. 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. Split Email Ids into Username and Domain Name
  3. Get the Root Domain from URL
  4. Convert Text to Numbers
  5. Extract First five Characters of a String

Text to Columns in Excel

Split cells in multiple column 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. Click here to know how to split cells in excel

what is Text to Column

Split Email Ids into Username and Domain Name:

With this wizard, you can select your own delimiter to split text.

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

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 using text to column wizard:

  • Select the data set
  • Go to Data> Data Tools
  • Go to Text to Columns
  • 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.

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

Extract Characters of a String

There are times when you want to extract only certain characters from a string. Since, 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.

Suppose, where you wish to extract the first few characters of a string. Although, 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 T2C

Steps to extract the first five character from the dataset using split cells:

  • Select the data set
  • Go to Data> Data Tools
  • Go to Text to Columns
  • 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