How to split a list of First and Last Names into multiple columns without using Text to Columns. Instead, Microsoft 2013 has a brand new method called Flash Fill. Watch the video and get ecstatic!
I appreciate you sooo much! I have been stuck on this for over two months, at work there's all these files and different uploads need different formats. The best part is your excitement, it's great to learn from people who geek out about there topic.
WHAT. This still works in 2019, I am baffled hahaha. I thought you were exaggerating with how excited I'd be lol, but alas, I made audible comments to myself how great it was. Thank you!
My data is coming in this format--- "HARDER, Mr. JESSE L" ----with all the names, the "Mr. and Mrs." in my list of names is throwing my (Left, Right and Mid) formula off can you give me any insight on how to by pass this issue? It uses "Mr." as my (MID) formula. and my (RIGHT) formula does not separate the first name from the middle initial. It keeps them together with the first name. I need first/Middle/Last name in separate cells.
If column A has a list of names and all of them have Middle Initials or Middle Names, it will work. However, if Column A has a mixture of "First Name Last Name" and "First Name Middle Initial Last Name" (or "First Name Middle Name Last Name), it will not work properly. Column B will still consist of First Names. But, Column C will have a mixture of Last Names (for names with no middle name) and Middle Names (for names with a middle name). Column D will only contain Last Names IF Column C has a Middle Name. Example: A B C D Bob Young Bob Young Bob E. Young Bob E. Young You can sort on column C (Which will contain only Last Names IF Column B has Middle Names). Then, Select and Move all remaining last names from Column B to Column C. If you would like, I can create a video for you.
This method works mostly with 2 word names (i.e. Joe Smith). But, as you have discovered, it doesn't work on every name. So, for names that are longer than two words: Each word will go into a separated cell.