|
You can do it with cell formulas but it will still require manual review unless you only have simple First Name Last Name combinations. If you have anything more complex the formulas quickly get more complex and are never foolproof.
Billy Bob Thornton is Billy Bob as the first name and Thornton as the last.
Kirsten Scott Thomas is Kirsten as the first name and Scott Thomas as the last.
You'll never make a straight formula that can correctly divine the difference between those to (and then won't get confused when James Van Der Beek gets cast in the movie and they all go to Rev. James Russell III for personal advice).
If you do want to do it with formulas this would work for pulling the first name out of "John Doe" (this assumes John Doe is in cell A1):
=LEFT(A1,FIND(" ",A1,1)-1))
should return John. Essentially the formula says "Find the string value of the first space in A1 and subtract one (call that "X"). Then take the first X characters in A1.
To turn it around and find the last name:
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))
This one says "Find the string value of the first space in A1 and subtract that number from the total length of the string in A1, call that number X. Then take the last X characters in A1.
I did those off the top of my head and don't have Excel at home so I can't test those but they should work. Again, though, they only work on the simplest of names. If the data includes more complex names these will consider everything before the first space to be the first name and everything after to be the last name. So Billy Bob Thornton would end up Billy/Bob Thornton rather than Billy Bob/Thornton.
I have some more complex forumulas I use at work so if you want I can pass them along if you remind me tomorrow. But eyeballing the result is still necessary.
|