Friday, March 23, 2007

Split names by using Convert Text to Columns in Microsoft Excel 2003 onwards

Long back I was attending a product launch seminar of Microsoft and heard one speaker saying that we dont use even 10% of commands/facilities/techniques available in Microsoft Office and he was not wrong when I faced a problem of splitting a column having full name of people into two/three columns of first/middle/last names and splitting a column having email addresses into two columns of username and the domain of the email addresses. I was thinking to write a script to do this work but then it was readily available in Microsoft Excel which I came to know after having already spent good time on this problem. Under the tools of Excel, there is a tool called Text to columns which has this power of splitting and does it in seconds. Steps to be followed are:

1. select the complete to-be-split-column
2. Click Text to columns under Tools menu of Excel
3. This gives you two identifiers viz. fixed width or delimited.
4. Because "Space" is a delimiter separating first name from middle name and last name, select delimited. Other delimitors like @ of email addresses come under "other" next to which you are supposed to enter this character @.
5. Make sure that you have already inserted empty columns next to the name/email column so that split results get accomodated.
6. If you select 'Space' as delimiter then just click next and identify the first column in the appropriate place of the dialogue box where first split results get accomodated and rest splits will automatically get accomodated in columns next to first split column.

No comments: