software

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.
Try something like this:

D1: =TRIM(MID(LEFT(A1,MIN(SEARCH({quot; W quot;,quot; L quot;},A1amp;quot; W L
quot;))-2),SEARCH(quot;@quot;,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-Proquot;Jambruinsquot; wrote:

gt; I have the following in cells A1:A4:
gt;
gt; 04/03/2005 @ NY Yankees L 2-9 D. Wells R. Johnson 180/8.5o -120 L/O
gt; 04/05/2005 @ NY Yankees L 3-4 M. Clement C. Pavano 140/9.5o -125 L/U
gt; 04/06/2005 @ NY Yankees W 7-3 T. Wakefield M. Mussina 151/10o -115 W/P
gt; 04/08/2005 @ Toronto W 6-5 B. Arroyo D. Bush -130/10o -120 W/O
gt;
gt; I would like cells D14 to find the team in cells A1:A4 and enter that into
gt; D14. Any idea how to do this? Thanks.
gt;
gt;

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.
quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; D1: =TRIM(MID(LEFT(A1,MIN(SEARCH({quot; W quot;,quot; L quot;},A1amp;quot; W L
gt; quot;))-2),SEARCH(quot;@quot;,A1) 2,255))
gt;
gt; Notice the spaces before and after the W's and the L's
gt;
gt; Copy that formula down as far as you need.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Jambruinsquot; wrote:
gt;
gt; gt; I have the following in cells A1:A4:
gt; gt;
gt; gt; 04/03/2005 @ NY Yankees L 2-9 D. Wells R. Johnson 180/8.5o -120 L/O
gt; gt; 04/05/2005 @ NY Yankees L 3-4 M. Clement C. Pavano 140/9.5o -125 L/U
gt; gt; 04/06/2005 @ NY Yankees W 7-3 T. Wakefield M. Mussina 151/10o -115 W/P
gt; gt; 04/08/2005 @ Toronto W 6-5 B. Arroyo D. Bush -130/10o -120 W/O
gt; gt;
gt; gt; I would like cells D14 to find the team in cells A1:A4 and enter that into
gt; gt; D14. Any idea how to do this? Thanks.
gt; gt;
gt; gt;

On Mon, 20 Mar 2006 10:51:30 -0800, Jambruins
gt; wrote:

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

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,quot;[A-Z]([\sA-Za-z0-9]) (?=\s[LW]\s)quot;)

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,quot;[A-Z]([\sA-Za-z0-9]) (?=\s[LW]\s)quot;))--ron

On Mon, 20 Mar 2006 10:51:30 -0800, Jambruins
gt; wrote:

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

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

Then use the formula:

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

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,quot;[A-Z]([\sA-Za-z0-9]) (?=\s[LW]\s)quot;))--ron

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-Proquot;Jambruinsquot; wrote:

gt; Ron,
gt; That works great except I should have added another row as some of the
gt; rows do not have the @ symbol. Here is an example of a row without the @
gt; symbol: 05/08/2005 Seattle L 4-6 R. Franklin (R) W. Miller (R) -140/10u
gt; -125 L/P
gt;
gt; How would I change the formula? Thanks for your help.
gt;
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; D1: =TRIM(MID(LEFT(A1,MIN(SEARCH({quot; W quot;,quot; L quot;},A1amp;quot; W L
gt; gt; quot;))-2),SEARCH(quot;@quot;,A1) 2,255))
gt; gt;
gt; gt; Notice the spaces before and after the W's and the L's
gt; gt;
gt; gt; Copy that formula down as far as you need.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Jambruinsquot; wrote:
gt; gt;
gt; gt; gt; I have the following in cells A1:A4:
gt; gt; gt;
gt; gt; gt; 04/03/2005 @ NY Yankees L 2-9 D. Wells R. Johnson 180/8.5o -120 L/O
gt; gt; gt; 04/05/2005 @ NY Yankees L 3-4 M. Clement C. Pavano 140/9.5o -125 L/U
gt; gt; gt; 04/06/2005 @ NY Yankees W 7-3 T. Wakefield M. Mussina 151/10o -115 W/P
gt; gt; gt; 04/08/2005 @ Toronto W 6-5 B. Arroyo D. Bush -130/10o -120 W/O
gt; gt; gt;
gt; gt; gt; I would like cells D14 to find the team in cells A1:A4 and enter that into
gt; gt; gt; D14. Any idea how to do this? Thanks.
gt; gt; gt;
gt; gt; gt;

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-Proquot;Ron Coderrequot; wrote:

gt; OK...here's another option that just occurred to me...
gt;
gt; There's a finite list of names you expect to find, right?
gt; (I'll assume your answer is: YES)
gt;
gt; E1: NY Yankees
gt; E2: Toronto
gt; E3: Seattle
gt;
gt; A1: (contains some text that contains a team name)
gt; B1: =INDEX($E$1:$E$3,SUMPRODUCT(--ISNUMBER(SEARCH($E$1:$E$3,A1))))
gt;
gt; Whichever team name is contained in A1, that team will be displayed in B1.
gt; I'm assuming that only ONE team will be listed in a cell.
gt;
gt; Add to the team name list and adjust range references to suit your situation.
gt;
gt; Is that something you can work with??
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Jambruinsquot; wrote:
gt;
gt; gt; Ron,
gt; gt; That works great except I should have added another row as some of the
gt; gt; rows do not have the @ symbol. Here is an example of a row without the @
gt; gt; symbol: 05/08/2005 Seattle L 4-6 R. Franklin (R) W. Miller (R) -140/10u
gt; gt; -125 L/P
gt; gt;
gt; gt; How would I change the formula? Thanks for your help.
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try something like this:
gt; gt; gt;
gt; gt; gt; D1: =TRIM(MID(LEFT(A1,MIN(SEARCH({quot; W quot;,quot; L quot;},A1amp;quot; W L
gt; gt; gt; quot;))-2),SEARCH(quot;@quot;,A1) 2,255))
gt; gt; gt;
gt; gt; gt; Notice the spaces before and after the W's and the L's
gt; gt; gt;
gt; gt; gt; Copy that formula down as far as you need.
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Jambruinsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have the following in cells A1:A4:
gt; gt; gt; gt;
gt; gt; gt; gt; 04/03/2005 @ NY Yankees L 2-9 D. Wells R. Johnson 180/8.5o -120 L/O
gt; gt; gt; gt; 04/05/2005 @ NY Yankees L 3-4 M. Clement C. Pavano 140/9.5o -125 L/U
gt; gt; gt; gt; 04/06/2005 @ NY Yankees W 7-3 T. Wakefield M. Mussina 151/10o -115 W/P
gt; gt; gt; gt; 04/08/2005 @ Toronto W 6-5 B. Arroyo D. Bush -130/10o -120 W/O
gt; gt; gt; gt;
gt; gt; gt; gt; I would like cells D14 to find the team in cells A1:A4 and enter that into
gt; gt; gt; gt; D14. Any idea how to do this? Thanks.
gt; gt; gt; gt;
gt; gt; gt; gt;

thanks for the help!

quot;Ron Coderrequot; wrote:

gt; YIKES! Right idea...wrong formula!
gt;
gt; B1:
gt; =INDEX($E$1:$E$3,SUMPRODUCT(--ISNUMBER(SEARCH($E$1:$E$3,A1))*ROW($E$1:$E$3)))
gt;
gt; It can probably be done more cleanly, but I'm pressed for time right now.
gt;
gt; I hope that helps.
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; OK...here's another option that just occurred to me...
gt; gt;
gt; gt; There's a finite list of names you expect to find, right?
gt; gt; (I'll assume your answer is: YES)
gt; gt;
gt; gt; E1: NY Yankees
gt; gt; E2: Toronto
gt; gt; E3: Seattle
gt; gt;
gt; gt; A1: (contains some text that contains a team name)
gt; gt; B1: =INDEX($E$1:$E$3,SUMPRODUCT(--ISNUMBER(SEARCH($E$1:$E$3,A1))))
gt; gt;
gt; gt; Whichever team name is contained in A1, that team will be displayed in B1.
gt; gt; I'm assuming that only ONE team will be listed in a cell.
gt; gt;
gt; gt; Add to the team name list and adjust range references to suit your situation.
gt; gt;
gt; gt; Is that something you can work with??
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Jambruinsquot; wrote:
gt; gt;
gt; gt; gt; Ron,
gt; gt; gt; That works great except I should have added another row as some of the
gt; gt; gt; rows do not have the @ symbol. Here is an example of a row without the @
gt; gt; gt; symbol: 05/08/2005 Seattle L 4-6 R. Franklin (R) W. Miller (R) -140/10u
gt; gt; gt; -125 L/P
gt; gt; gt;
gt; gt; gt; How would I change the formula? Thanks for your help.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; D1: =TRIM(MID(LEFT(A1,MIN(SEARCH({quot; W quot;,quot; L quot;},A1amp;quot; W L
gt; gt; gt; gt; quot;))-2),SEARCH(quot;@quot;,A1) 2,255))
gt; gt; gt; gt;
gt; gt; gt; gt; Notice the spaces before and after the W's and the L's
gt; gt; gt; gt;
gt; gt; gt; gt; Copy that formula down as far as you need.
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Jambruinsquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have the following in cells A1:A4:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; 04/03/2005 @ NY Yankees L 2-9 D. Wells R. Johnson 180/8.5o -120 L/O
gt; gt; gt; gt; gt; 04/05/2005 @ NY Yankees L 3-4 M. Clement C. Pavano 140/9.5o -125 L/U
gt; gt; gt; gt; gt; 04/06/2005 @ NY Yankees W 7-3 T. Wakefield M. Mussina 151/10o -115 W/P
gt; gt; gt; gt; gt; 04/08/2005 @ Toronto W 6-5 B. Arroyo D. Bush -130/10o -120 W/O
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I would like cells D14 to find the team in cells A1:A4 and enter that into
gt; gt; gt; gt; gt; D14. Any idea how to do this? Thanks.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;