I have a cell with the following info in it and i want to seperate the
text to another 2 cells according to where certain characters fall
(vs):

St. George's vs Sliema W.

I want the end result to show St. George's in one cell and Sliema W. in
another (adjacent to it)
I'm trying to use the FIND(find_text,within_text,start_num) in
combination with RIGHT(text,num_chars) but i cannot seem to sort it out
so as to come to the desired result!
Can any1 help?

M

=LEFT(A1,FIND("vs",A1))

=MID(A1,FIND("vs",A1)+2,99)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MMuscat" wrote in message
ps.com...
I have a cell with the following info in it and i want to seperate the
text to another 2 cells according to where certain characters fall
(vs):

St. George's vs Sliema W.

I want the end result to show St. George's in one cell and Sliema W. in
another (adjacent to it)
I'm trying to use the FIND(find_text,within_text,start_num) in
combination with RIGHT(text,num_chars) but i cannot seem to sort it out
so as to come to the desired result!
Can any1 help?

M

Hi M,

One way, assuming the info is in cell a1 is to have in say cell b1

=LEFT(a1,FIND(" vs ",a1)-1)

and in say cell c1

=RIGHT(a1,LEN(a1)-FIND(" vs ",a1)-3)

Anthony
(adapted from David Hepner 9/13/2005)

"MMuscat" wrote:

I have a cell with the following info in it and i want to seperate the
text to another 2 cells according to where certain characters fall
(vs):

St. George's vs Sliema W.

I want the end result to show St. George's in one cell and Sliema W. in
another (adjacent to it)
I'm trying to use the FIND(find_text,within_text,start_num) in
combination with RIGHT(text,num_chars) but i cannot seem to sort it out
so as to come to the desired result!
Can any1 help?

M

Thanks guys! both work! Anthony's gives a better result!

good day!


Anthony D wrote:
Hi M,

One way, assuming the info is in cell a1 is to have in say cell b1

=LEFT(a1,FIND(" vs ",a1)-1)

and in say cell c1

=RIGHT(a1,LEN(a1)-FIND(" vs ",a1)-3)

Anthony
(adapted from David Hepner 9/13/2005)

"MMuscat" wrote:

I have a cell with the following info in it and i want to seperate the
text to another 2 cells according to where certain characters fall
(vs):

St. George's vs Sliema W.

I want the end result to show St. George's in one cell and Sliema W. in
another (adjacent to it)
I'm trying to use the FIND(find_text,within_text,start_num) in
combination with RIGHT(text,num_chars) but i cannot seem to sort it out
so as to come to the desired result!
Can any1 help?

M

If it is that simple you change the " vs " to "|"
and then use text to columns under Data, to separate
based on the character delimiter of "|" with no messy
cleanup afterwards to remove formulas and eliminate
the original column.

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"MMuscat" wrote...
I'm trying to use the FIND(find_text,within_text,start_num) in
combination with RIGHT(text,num_chars) but i cannot seem to sort it out
so as to come to the desired result!

Perhaps a user-settable string delimiter could be useful as well in Text to
Columns,
currently a single character delimiter as 'Other' (e.g. v) seems ok (but not
more than one) ?

Anthony

"David McRitchie" wrote:

If it is that simple you change the " vs " to "|"
and then use text to columns under Data, to separate
based on the character delimiter of "|" with no messy
cleanup afterwards to remove formulas and eliminate
the original column.

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"MMuscat" wrote...
I'm trying to use the FIND(find_text,within_text,start_num) in
combination with RIGHT(text,num_chars) but i cannot seem to sort it out
so as to come to the desired result!