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.

 

[Guest]

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

Regards,
Stefi

„shane24†ezt írta:

 

[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


??

 

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

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