Monday, December 16, 2019

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

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

December 16, 2019 Excel tips,

Text to Columns in Excel

Split cells in multiple columns is a feature in Excel. It will help you to Split your Text into the 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

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 email 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

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

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

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

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.

📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1