Splitting text in a cell
 

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.

Splitting text in a cell
 

They can still be used but it is better to use MID instead of RIGHT perhaps

=TRIM(LEFT(A1,SEARCH("and",A1)-1))


for first name


=TRIM(MID(A1,SEARCH("and",A1)+3,255))


for second


copy both formulas down and then copy and paste special as values in place
to make them, independent of the source string


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

Splitting text in a cell
 

Peo -

Probably safer to use " and " as the search string, to protect against
splitting names like Anderson or Sanderson where you don't want to.



"Peo Sjoblom" wrote:

They can still be used but it is better to use MID instead of RIGHT perhaps

=TRIM(LEFT(A1,SEARCH("and",A1)-1))


for first name


=TRIM(MID(A1,SEARCH("and",A1)+3,255))


for second


copy both formulas down and then copy and paste special as values in place
to make them, independent of the source string


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

Splitting text in a cell
 

Definitely, good catch Duke

Obviously that would change the formulas a bit when it comes to the
subtract/added offsets to the search part

Peo



"Duke Carey" wrote in message
...
Peo -

Probably safer to use " and " as the search string, to protect against
splitting names like Anderson or Sanderson where you don't want to.



"Peo Sjoblom" wrote:

They can still be used but it is better to use MID instead of RIGHT
perhaps

=TRIM(LEFT(A1,SEARCH("and",A1)-1))


for first name


=TRIM(MID(A1,SEARCH("and",A1)+3,255))


for second


copy both formulas down and then copy and paste special as values in
place
to make them, independent of the source string


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

Splitting text in a cell
 

Thanks, this was really helpful. I didn't even know there was such a function
as TRIM before! Thank you very much.

"Peo Sjoblom" wrote:

They can still be used but it is better to use MID instead of RIGHT perhaps

=TRIM(LEFT(A1,SEARCH("and",A1)-1))


for first name


=TRIM(MID(A1,SEARCH("and",A1)+3,255))


for second


copy both formulas down and then copy and paste special as values in place
to make them, independent of the source string


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