Share on Facebook

Share on Twitter

Share on LinkedIn

Share on Email

Share More


CA CS CIMA Prakash Somani (Landmark Group) (23477 Points)

02 April 2011  



Have you ever found yourself wondering what in the world possessed the person who created your MS Excel file to put both first and last name 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 “forever” if your list is really long.


Or… maybe you have other text / data that really should be separated into different cells for easy sorting and editing.


Would you like a fast way to separate the data? (This works best with data that’s pretty consistent and not too complicated.)


Most of us would say “yes”. (Even if you don’t have an immediate use for this one, it’s one of those handy little pieces of information that may just get you out of a jam one day. 


So let’s get to that faster way…


The first thing you’ll need to do is to highlight the data that needs to be separated.


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


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


On step 1 check at the top that the data will be delimited.

Then click Next.


In step 2 you need to check the Space option in the Delimiters section. (Make sure all other choices are unchecked.) Basically what you’re doing here is telling Excel what character separates your pieces of data—so that when it find a space it knows to stick the next piece of text in a new column.


Did you notice the preview of your data in the bottom window? See how the names are now divided into different columns?


Click Next.

Step 3 will allow you to do some formatting and placement of your data—but for now, as beginners, just click the Finish button.




Just like magic your first and last names are in two separate columns, ready to sort – no retyping necessary.


(Ok—maybe not magic but, if your list was long, you’ll feel like it was when you realize how much time you just saved.)

 3 Replies

CA CS CIMA Prakash Somani (Landmark Group) (23477 Points)
Replied 02 April 2011

The same you can do for any text file say your bank statement in text format which you you directly paste in excel file and arrange the data using above function..


let me know if any help needed....

Gobish Gopalakrishnan (Financial Analyst) (276 Points)
Replied 05 April 2011

Originally posted by : CA CS Prakash Somani (A helpin

 Thanks for the tips. Also find some links in the post


Babita Jain (Personal Assistant) (153 Points)
Replied 14 May 2021

Take text in one or more cells and split it into multiple cells using the Convert Text to Columns Wizard.

Process -

  1. Select the cell or column that contains the text you want to split.
  2. Select Data > Text to Columns.
  3. In the Convert Text to Columns Wizard, select Delimited > Next.
  4. Select the Delimiters for your data. For example, Comma and Space. You can see a preview of your data in the Data preview window.
  5. Select Next.
  6. Select the Destination in your worksheet which is where you want the split data to appear.
  7. Select Finish.

Full Excel Tutorials - click

Leave a reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Subscribe to the latest topics :
Search Forum:

Trending Tags