Hello,
In Excel 2007, I have a series of cells with the names of two people in them
in the format of "Person One and Person Two". Is there any function which
could put each person's name in a different cell, by looking for the word
"and" to use to decide where to split the text?
I know that if each persons name was the same length, I could use the LEFT
and RIGHT functions, but as name lengths vary, I cannot use these functions
for this.
Thank you.

Hi,

assumed your data is like: first name+one space+and+onespace+second name
and your data is in column A

for the first name in the cell B2 enter formula below and then copy drag
down to where you need:
=LEFT(A1,Find("and",A1)-2)
for the second name in the cell C2 enter formula below and then copy drag
down to where you need:
=RIGHT(A1,LEN(A1)-3-FIND("and",A1))

Thanks,
--
Farhad Hodjat


"Richard_123" wrote:

Hello,
In Excel 2007, I have a series of cells with the names of two people in them
in the format of "Person One and Person Two". Is there any function which
could put each person's name in a different cell, by looking for the word
"and" to use to decide where to split the text?
I know that if each persons name was the same length, I could use the LEFT
and RIGHT functions, but as name lengths vary, I cannot use these functions
for this.
Thank you.

A1 - C1 are the headings: Employee Name; FNAme; LName
A2 Baird, Deborah
B2 Enter this formula: =LEFT(A2,((SEARCH(",",A2)))-1)
C2 Enter this formual: =RIGHT(A2,(LEN(A2))-(SEARCH(",",A2)))

B2 will have "Baird"
C2 will display "Deborah"

Copy the contents of B2:C2 as far down the worksheet as you need.

"Richard_123" wrote:

Hello,
In Excel 2007, I have a series of cells with the names of two people in them
in the format of "Person One and Person Two". Is there any function which
could put each person's name in a different cell, by looking for the word
"and" to use to decide where to split the text?
I know that if each persons name was the same length, I could use the LEFT
and RIGHT functions, but as name lengths vary, I cannot use these functions
for this.
Thank you.

Please refrain from multiposting the same message in multiple newsgroups


--
Regards,

Peo Sjoblom



"Richard_123" wrote in message
...
Hello,
In Excel 2007, I have a series of cells with the names of two people in
them
in the format of "Person One and Person Two". Is there any function which
could put each person's name in a different cell, by looking for the word
"and" to use to decide where to split the text?
I know that if each persons name was the same length, I could use the LEFT
and RIGHT functions, but as name lengths vary, I cannot use these
functions
for this.
Thank you.

See if this does what you want.

=RIGHT(A1,FIND(" ",A1)+1)

HTH
Regards,
Howard

"Richard_123" wrote in message
...
Hello,
In Excel 2007, I have a series of cells with the names of two people in
them
in the format of "Person One and Person Two". Is there any function which
could put each person's name in a different cell, by looking for the word
"and" to use to decide where to split the text?
I know that if each persons name was the same length, I could use the LEFT
and RIGHT functions, but as name lengths vary, I cannot use these
functions
for this.
Thank you.