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 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
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:
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:
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.
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.
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:
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.
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 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:
Wow! Text to Column has Converted the Numbers back into the General Format. And these numbers are good to use in the Formulas.
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
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: