Separate Content of One Excel Cells into Separate 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.

Split Email Ids into Username and Domain Name:

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:

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

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:

If an Apostrophe has been put before the number. Here, the Number will convert into Text

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:

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

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

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. You don’t need to strikethrough the cells in excel

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.

About Us

We offer instructional videos, practice files, and a personalized learning dashboard that empower an individual to master use cases of the software at their own pace anytime anywhere. We work with 20+ industry experts who double up as award-winning instructors for our courses.