Find Text within a cell

  • Thread starter Thread starter MMuscat
  • Start date Start date
M

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
 
=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)
 
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)
 
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.
 
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
 
Back
Top