Need Excel Help (again)

Kevy Baby · 12-10-2007, 09:07 PM

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 · 12-10-2007, 09:14 PM

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 · 12-10-2007, 09:41 PM

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

TYVM!

lashbear · 12-10-2007, 09:53 PM

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 · 12-10-2007, 09:55 PM

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 · 12-10-2007, 09:55 PM

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

Kevy Baby · 12-10-2007, 10:35 PM

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 · 01-11-2008, 09:20 PM

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.

Deebs · 01-12-2008, 03:24 AM

Excel is of the devil.

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

Sub la Goon · 01-12-2008, 08:23 AM

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

12-10-2007, 09:07 PM	#1
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	Need Excel Help (again) 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? __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

12-10-2007, 09:14 PM	#2
scaeagles I LIKE! Join Date: Jan 2005 Posts: 7,819	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.
	Submit to Quotes

12-10-2007, 09:41 PM	#3
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	DOH! I was trying to take a simple task and make it difficult. TYVM! __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

12-10-2007, 09:53 PM	#4
lashbear SwishBuckling Bear Join Date: Jan 2005 Location: In Isolation :) Posts: 6,597	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. __________________ *I Heart* my Husband - I can't think of anyone I'd rather be in isolation with.**
	Submit to Quotes

12-10-2007, 09:55 PM	#5
Alex . Join Date: Feb 2005 Posts: 13,354	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.
	Submit to Quotes

12-10-2007, 10:35 PM	#7
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	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. __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

01-11-2008, 09:20 PM	#8
lindyhop Swing Swank Join Date: Jan 2005 Location: Long Beach Posts: 997	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. __________________ Hyperbole is the best thing ever!
	Submit to Quotes

01-12-2008, 03:24 AM	#9
Deebs ... Join Date: Dec 2007 Posts: 961	Excel is of the devil. But I'm glad you guys are makin' it work for 'ya!
	Submit to Quotes

01-12-2008, 08:23 AM	#10
Sub la Goon Ride me! Join Date: Jul 2006 Location: The line forms here... Posts: 326	In our next lesson, we will learn how to do the opposite: Concatenate! __________________ You roll the dice, you move your mice.
	Submit to Quotes