How to separate first and last names entered in one cell into two different cells

The database that I'm working on divides patient names into three separate columns: lastName, firstName, midInit. My department has several old spreadsheets that have data my boss suddenly wants included in my database. The trouble is, these old spreadsheets have the patient's last name, first name, and middle initial entered into the same column. There are at least 200 hundred names in these spreadsheets. I absolutely do not want to go back and separate the names into separate columns by hand.

Is there any way to get Excel to take the names entered into one cell and split them into three cells?

I've attached a spreadsheet example that shows what I need to do.

Originally Posted by modytrane

look at the file attached.
I am sure someone will give you a better solution.
But it's a start. You'll get the idea.
modytrane.

It works pretty well!

The only problem is that if a name has no middle initial then the formula breaks off the last two letters of the first name and puts them into the midInit column. So Williams, Jimmy becomes:

Williams | Jim | my

Originally Posted by martindwilson

well first stab
allowing for fact you have a rogue full stop and odd spaces
for last name
=LEFT(D4,FIND(",",D4)-1)
for first name
=TRIM(LEFT(MID(D4,FIND(",",D4)+1,255),FIND(" ",TRIM(MID(D4,FIND(",",D4)+1,255)))))
and for initial
=TRIM(RIGHT(D4,2))

Thanks very much.

Your formula for the middle initial appears to solve the problem I mentioned in reply to Modytrane above.

Thanks to both of you!

-Edit-

Oops may have spoke too soon.

Please Login or Register  to view this content.

Also breaks off the last two letters of the first name if the name has no middle initial.

insert 2 columns after list of names
select column with names
find /replace fullstop with nothing
select column again
data/text to columns
delimited
check "space",check "other "put a ,(comma) in box next to "other", make sure the treat consecutive delimiters as one is ticked ,click finish