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.
The menu is availble under Data Tab of the ribbon menu.
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.
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))