Use delimiters for breaking text into columns in Excel

Microsoft Excel text 2 Columns Menu

Microsoft Excel text 2 Columns Menu

Lets say you have a line which you had copied from somehwere and contains data like First Name, Second Name and City. Now once you put it in excel and you want to seperate out in diff columns , this will be a difficult job, Specially when you have lets say 300 lines of such data.

Its time you should know Columns to Text Feature of Micrsoft Office Excel 2007. This helps you to break along text into columns of excel based on certain de-limiters. Most of the time you will have just on delimiter in these cases. You can save a lot of time doing this.

Converting Text 2 columns in MS office 2007 Excel

Converting Text 2 columns in MS office 2007 Excel

The menu is availble under Data Tab of the ribbon menu.


About Ashish Mohta

A Professional Tech blogger, Editor and Writer who talks about solving day to day problems of people who use computer. His expertise are in Windows 7, Microsoft Office, Software, Mobile Apps and Video Posts.

3 comments

  1. I need to use delimiter in excel but not for the whole content of the cell. Like in the text there are two or more space and I just want to separate the text where the first space is like:

    Chair broken handle to convert to two columns Chair and Broken handle
    When I use delimiter excel makes three columns out of this example. I appreciate your help.

  2. I want to apply delimit condition at last instance or event only.

    for eg

    S K Pradhan after applying delimiter it should be appear as
    S K in one column and Pradhan in other Column.

    Regards,

    Vaibhav

    • Hi Vaibhav,

      You cannot do it using the delimitor condition. But use a formula instead

      In A1 add the full name.

      To get the first/middle name, try this in cell B1 = LEFT(A1,SEARCH(C1,A1,1)-1)

      To get the surname, try this in cell C1 = TRIM(RIGHT(SUBSTITUTE(A1,” “,REPT(” “,99)),20))

Leave a Reply