Advance Excel Learning - Text to Column function - Software Applications - MS Excel - TIK Share

Advance Excel Learning - Text to Column function


CA Pulkit Sharma

2013-05-07

This is very important Data handling Tool. If you are not good with this function, you may waste 85% of your office time doing things manually.

 

Text to Column function is used to separate the content of one cell into different cells.

 

This function is highly useful when we Export Data from Accounting System, Website etc.

 

And also sometime when you copy and paste some data in Excel Data is captured  in one cell but you need Data in different cells.

 

Lets learn how to use this function.

 

Example:

Amar Akbar Anthony Ram Shyam Geeta Sheeta

 

you can see that the above names are in a single cell, but you need to write them in different cells.

you can do this job manually by typing the names in different cells. But what if you have  thousands of name to type.

 

Don’t worry this can be done be with help of Text to Column function.

To use the function select the cells whose data you want to break into different cells.

 

Amar Akbar Anthony Ram Shyam Geeta Sheeta

 

Now select DATA tab and click on Text to Columns function.

 

Text to column function of ms excel

 

 

Once this screen appears you have 2 options Delimited and Fixed Width.                         

Click on Delimited if words in cell does not have fixed space between them. Always Delimited is used more. As Data exported may be of varying type.

 

Click on fixed Width if your words has same differences and proceed further you will find instruction on Wizards itself.

 

Text to column function of MS excel

 

If you are seperating using Delimited option than select Delimited and click on next, on next screen you will get option as to at which point your words should be seperated. You can select out of Space, Tab, Comma etc. In our example you can see each name is seperated by a space so we will select space and finish the process.

 

Once you click on Finish the End result will be

 

Amar

Akbar

Anthony

Ram

Shyam

Geeta

Sheeta

 

 Download the original excel file to learn offline from the link text-to-columns.zip

 

 

Thank you