Find Text within a cell

[MMuscat]

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

 

[Bob Phillips]

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

 

[Anthony D]

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]

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

good day!

 

[David McRitchie]

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.

 

[Anthony D]

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