Opposite of Concatenate

[shane24]

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.

[JE McGimpsey]

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.1zel2n_1133511006.4671@excelforum-nospam.com>,
shane24 <shane24.1zel2n_1133511006.4671@excelforum-nospam.com> 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.

[shane24]

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 McGimpsey]

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

Data/Text to Columns/Delimited/Space character


??

In article <shane24.1zen5m_1133513704.5418@excelforum-nospam.com>,
shane24 <shane24.1zen5m_1133513704.5418@excelforum-nospam.com> 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

[shane24]

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.

[Ron Rosenfeld]

On Fri, 2 Dec 2005 03:25:10 -0600, shane24
<shane24.1zeory_1133515802.6939@excelforum-nospam.com> 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

[Stefi]

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
>
>