I fully understand how to use concatenate. However I am needing to find
out the opposite of it. I have a sheet where column A is the full name
of a customer. I want the name broken down into seperate cells for
first and last names.

What I have:
A1 = John Smith

What I want
B1 = John
C1 = Smith

Thanks for all your help.


--
shane24
------------------------------------------------------------------------
shane24's Profile: http://www.excelforum.com/member.php...fo&userid=4770
View this thread: http://www.excelforum.com/showthread...hreadid=490090

One way:

B1: =LEFT(A1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,255)

where 255 is just a large enough number to capture the remainder of the
text.

In article ,
shane24 wrote:

I fully understand how to use concatenate. However I am needing to find
out the opposite of it. I have a sheet where column A is the full name
of a customer. I want the name broken down into seperate cells for
first and last names.

What I have:
A1 = John Smith

What I want
B1 = John
C1 = Smith

Thanks for all your help.

B1 =LEFT(A1,SEARCH(" ",A1)-1)
C1 =RIGHT(A1,LEN(A1)-LEN(B1)-1)

Regards,
Stefi

€žshane24€� ezt Ã*rta:


I fully understand how to use concatenate. However I am needing to find
out the opposite of it. I have a sheet where column A is the full name
of a customer. I want the name broken down into seperate cells for
first and last names.

What I have:
A1 = John Smith

What I want
B1 = John
C1 = Smith

Thanks for all your help.


--
shane24
------------------------------------------------------------------------
shane24's Profile: http://www.excelforum.com/member.php...fo&userid=4770
View this thread: http://www.excelforum.com/showthread...hreadid=490090

Thank you very much. That works very well. However now that I look at
my list a little closer I have some fields that have a middle name or
initial. Is there a way that we can make this formula break down into
3 columns for first middle and last??

Thanks


--
shane24
------------------------------------------------------------------------
shane24's Profile: http://www.excelforum.com/member.php...fo&userid=4770
View this thread: http://www.excelforum.com/showthread...hreadid=490090

Do you need formulae, or could you just do a one-time

Data/Text to Columns/Delimited/Space character


??

In article ,
shane24 wrote:

Thank you very much. That works very well. However now that I look at
my list a little closer I have some fields that have a middle name or
initial. Is there a way that we can make this formula break down into
3 columns for first middle and last??

Thanks

JE thanks a lot for that. I didn't even realize that function existed
in Excel. I have found a few problems with that. I have some
customers that have multi word last names such as "De La Cruz" When I
use the menu function it seperates at every space and then creates 5 or
6 columns for that name. However ... I do have another very good use
for that menu function. It is still very helpful.


--
shane24
------------------------------------------------------------------------
shane24's Profile: http://www.excelforum.com/member.php...fo&userid=4770
View this thread: http://www.excelforum.com/showthread...hreadid=490090

On Fri, 2 Dec 2005 03:25:10 -0600, shane24
wrote:


JE thanks a lot for that. I didn't even realize that function existed
in Excel. I have found a few problems with that. I have some
customers that have multi word last names such as "De La Cruz" When I
use the menu function it seperates at every space and then creates 5 or
6 columns for that name. However ... I do have another very good use
for that menu function. It is still very helpful.

It would be easy to get first and last words; it would be easy to get first;
last; and everything else words.

But in order to split out multiple word last names, I think you'd have to have
a table of words/phrases that are considered to be part of a last name, in
order to separate it from the middle name. Is your project such that you want
to develop that?


--ron