Monday, July 26, 2010

Split text among Clumns in MS Excel - Useful Computer Trick

Suppose you have some data in word file or notepad about some person like first name, last name, age etc... Now you have to sort this data according to last name. This problem is very easy and can be solved easily by using MS Excel but what happens is when you copy paste whole content in Ms Excel file it will first and last name and age in the same cell.

Now how are you supposed to sort that list alphabetically by last name when first name is listed first?


The answer is that you can't you have to separate the first and last names somehow...
But how?

You could spend your time retyping the last name into the next column. It works but it's not a great plan since it could take long time if your list is really long.

So here is a fast way to separate the data, It will split text among columns in MS Excel. Make sure text should be deliminated( seperated by spcae or tab)Just follow these steps:

1). First copy paste content in MS Excel file.


2). Now Highlight the data that needs to be separated as shown in figure.


3). Now go to the Data menu, Text into Columns choice.


4). A 3-step wizard will start to guide you through the process.


5). On step 1 check at the top that the data will be delimited, Then click Next.


6). In step 2 you can select Deliminater (space or tab or any other) in the Delimiters section.
Deliminater tells Excel what character separates your pieces of data. If you select space as deliminater then when it find a space it place next piece of text in a new column.

It also shows preview of your data in the bottom window..

7). Click Next.


8). Click Finish.



Its done.

Just like magic your first and last names and ages are in separate columns,now you can sort them easily...



No comments:

Post a Comment