In response to my post on splitting names into multiple columns in Excel, someone (who listed themselves as “me”) posted the following comment:
very interesting, but there is a problem – when you split the names into two or three columns using this method and there are variations of first+last and first+middle+last names within the column, it tends to insert the middle names of those who have them in the same column with the last names of those who don’t have middle names, reserving the third column for the last names of those who have middle names. you end up with a mixed column -not good.
I did see this problem when I used the feature, but it was a simple matter to sort the list by last name and then, for the ones that were empty, move the entry from middle name to the last name column. It took me all of a minute and a half.
David Campeau commented:
And would you happen to know how to turn the feature off?
This is the most usefull feature that bugs me in the long run. After using it, I will have to close Excel than reopen it because it keeps trying to “help” me by doing more and more Text to column on each paste in the range!
I dont know if it saved me more time than it wasted me in the long run!
My friend (and former manager) Ross just supplied me with an answer to this one:
The solution to the “problem” described in the comments is to do another Text To Columns, choose Delimited, and restore the defaults (Tab checked and Space unchecked).
Hope this helps!