Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text within text
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 D1:D4 to find the team in cells A1:A4 and enter that into D1:D4. Any idea how to do this? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text within text
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" wrote: 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 D1:D4 to find the team in cells A1:A4 and enter that into D1:D4. Any idea how to do this? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text within text
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 Coderre" wrote: 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" wrote: 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 D1:D4 to find the team in cells A1:A4 and enter that into D1:D4. Any idea how to do this? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text within text
On Mon, 20 Mar 2006 10:51:30 -0800, Jambruins
wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text within text
On Mon, 20 Mar 2006 10:51:30 -0800, Jambruins
wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text within text
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 "Jambruins" wrote: 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 Coderre" wrote: 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" wrote: 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 D1:D4 to find the team in cells A1:A4 and enter that into D1:D4. Any idea how to do this? Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text within text
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 "Ron Coderre" wrote: 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 "Jambruins" wrote: 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 Coderre" wrote: 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" wrote: 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 D1:D4 to find the team in cells A1:A4 and enter that into D1:D4. Any idea how to do this? Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text within text
thanks for the help!
"Ron Coderre" wrote: 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 "Ron Coderre" wrote: 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 "Jambruins" wrote: 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 Coderre" wrote: 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" wrote: 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 D1:D4 to find the team in cells A1:A4 and enter that into D1:D4. Any idea how to do this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Matching Text In Col A move to Col B | Excel Worksheet Functions | |||
Find text in cell, copy row to new sheet | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |