Need Excel Help (again)

[Kevy Baby]

I really tried to figure this one out, but just couldn't find the right solution. I am 98% certain that this is doable in Excel and I am fairly certain that the solution involves the "LEFT" "MID" and/or "RIGHT" functions, but I seem to be missing something.

Basically, I want to be able to take a cell containing a first and last name and split it into two different cells (FName and LName).

What am I missing?

[scaeagles]

Highlight the cells.
go to data, text to columns
select delimited, push next
select space as the delimiter and push finish

This is assuming the names are separated by a spaces, such as "John Doe", Kevy Baby", etc.

[Kevy Baby]

DOH! I was trying to take a simple task and make it difficult.

TYVM!

[lashbear]

God, Leo, you are a genius. (this helped me out too...)

Remind me to ship that Toaster Oven out to you as as I can.

[Alex]

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.

[Alex]

Leo's solution works, obviously, but it will have the same problem with more complex first or last names.

[Kevy Baby]

Thanks Alex - that was the solution I was thinking of. I remember reading an article about it, but couldn't find it.

I know that either method (Leo's or Alex's) does the trick. I don't have enough names to work with (maybe 400 or so) on this round to worry about anything more complex - I can find the aberrations without too much fuss.

Thanks to both of you!!!



ETA to Alex: I have at my disposal a programmer who actually has written routines that CAN separate out the Billy Bob Thornton's and James Van Der Beek's (he primarily works with mailing databases). Some of the stuff he comes up with blows my mind. But I don't want to bug him for this stuff.

[lindyhop]

I had to really dig to figure out where I'd seen this little discussion but I want to say thanks a million for bringing up this question. I use Excel all the time but for very routine stuff. I didn't even realize you could do this. Anyway I had this really messy worksheet that had been copied from another source and I thought this would help me get data in one cell (a number and a name) into two separate cells. I called up the little paper clip guy and with just a couple of false starts I found the Text to Columns thingie. I couldn't use it for all the data but it really helped cut down the manual editing I had to do. And now I feel so smart.

:snap: :snap:

[Deebs]

Excel is of the devil.

But I'm glad you guys are makin' it work for 'ya!

[Sub la Goon]

In our next lesson, we will learn how to do the opposite: Concatenate!

[Kevy Baby]

In our next lesson, we will learn how to do the opposite: Concatenate!Oh, that's easy.