Find text within text

[Jambruins]

I have the following in cells A1:A4:

04/03/2005 @ NY Yankees L 2-9 D. Wells R. Johnson 180/8.5o -120 L/O
04/05/2005 @ NY Yankees L 3-4 M. Clement C. Pavano 140/9.5o -125 L/U
04/06/2005 @ NY Yankees W 7-3 T. Wakefield M. Mussina 151/10o -115 W/P
04/08/2005 @ Toronto W 6-5 B. Arroyo D. Bush -130/10o -120 W/O

I would like cells D14 to find the team in cells A1:A4 and enter that into
D14. Any idea how to do this? Thanks.

 

[Ron Coderre]

Try something like this:

D1: =TRIM(MID(LEFT(A1,MIN(SEARCH({" W "," L "},A1&" W L
"))-2),SEARCH("@",A1)+2,255))

Notice the spaces before and after the W's and the L's

Copy that formula down as far as you need.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Jambruins]

Ron,
That works great except I should have added another row as some of the
rows do not have the @ symbol. Here is an example of a row without the @
symbol: 05/08/2005 Seattle L 4-6 R. Franklin (R) W. Miller (R) -140/10u
-125 L/P

How would I change the formula? Thanks for your help.

 

[Ron Rosenfeld]

Ron,
That works great except I should have added another row as some of the
rows do not have the @ symbol. Here is an example of a row without the @
symbol: 05/08/2005 Seattle L 4-6 R. Franklin (R) W. Miller (R) -140/10u
-125 L/P

How would I change the formula? Thanks for your help.

It looks to me that one method of identifying the desired segment is that it
starts with the first capital letter in the string and ends with a capital L or
W that is surrounded by a <space>.

You can do this with regular expressions but you must download and install
Longre's free morefunc.xll add-in from

Then use the formula:

=REGEX.MID(A1,"[A-Z]([\sA-Za-z0-9])+(?=\s[LW]\s)")

Since the formula may leave a terminal space if there are two spaces between
the team name and the W or L, you could TRIM the result to eliminate that:

=TRIM(REGEX.MID(A1,"[A-Z]([\sA-Za-z0-9])+(?=\s[LW]\s)"))


--ron

 

[Ron Rosenfeld]

Ron,
That works great except I should have added another row as some of the
rows do not have the @ symbol. Here is an example of a row without the @
symbol: 05/08/2005 Seattle L 4-6 R. Franklin (R) W. Miller (R) -140/10u
-125 L/P

How would I change the formula? Thanks for your help.

It looks to me that one method of identifying the desired segment is that it
starts with the first capital letter in the string and ends with a capital L or
W that is surrounded by a <space>.

You can do this with regular expressions but you must download and install
Longre's free morefunc.xll add-in from http://xcell05.free.fr/

Then use the formula:

=REGEX.MID(A1,"[A-Z]([\sA-Za-z0-9])+(?=\s[LW]\s)")

Since the formula may leave a terminal space if there are two spaces between
the team name and the W or L, you could TRIM the result to eliminate that:

=TRIM(REGEX.MID(A1,"[A-Z]([\sA-Za-z0-9])+(?=\s[LW]\s)"))


--ron

 

[Ron Coderre]

OK...here's another option that just occurred to me...

There's a finite list of names you expect to find, right?
(I'll assume your answer is: YES)

E1: NY Yankees
E2: Toronto
E3: Seattle

A1: (contains some text that contains a team name)
B1: =INDEX($E$1:$E$3,SUMPRODUCT(--ISNUMBER(SEARCH($E$1:$E$3,A1))))

Whichever team name is contained in A1, that team will be displayed in B1.
I'm assuming that only ONE team will be listed in a cell.

Add to the team name list and adjust range references to suit your situation.

Is that something you can work with??

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Ron Coderre]

YIKES! Right idea...wrong formula!

B1:
=INDEX($E$1:$E$3,SUMPRODUCT(--ISNUMBER(SEARCH($E$1:$E$3,A1))*ROW($E$1:$E$3)))

It can probably be done more cleanly, but I'm pressed for time right now.

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Jambruins]

thanks for the help!

YIKES! Right idea...wrong formula!

B1:
=INDEX($E$1:$E$3,SUMPRODUCT(--ISNUMBER(SEARCH($E$1:$E$3,A1))*ROW($E$1:$E$3)))

It can probably be done more cleanly, but I'm pressed for time right now.

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP-Pro