How to use Text to Column in Excel with 4 Difference Example

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.

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