Searching for Substrings Within Strings
I would like to be able to search and flag a list of
string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear as indicated in Column B. Thanks in advance for your help. Column A Column B Column C ----------------- -------- ---------- A6520 04-02 X 6520 04-02 1511 03-01-VT X 1511 03-01 S6520 00 mtr-AAA-TR565 BC6520 04-02-01 X D6520 00-ABC 8951511 03-01-VTM X |
Tiziano wrote:
I would like to be able to search and flag a list of string data based on another list of substring data. Look at the FIND() or SEARCH() functions. The Help files will tell you all about them. Bill |
Hi Tiziano
Try this formulae in B column and i think your problem can be solved. =IF(ISERROR(FIND(C1,A1)),"","X") And then you can copy this formulae to any place in the B column where u want this operation to take place. Regards "Tiziano" wrote: I would like to be able to search and flag a list of string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear as indicated in Column B. Thanks in advance for your help. Column A Column B Column C ----------------- -------- ---------- A6520 04-02 X 6520 04-02 1511 03-01-VT X 1511 03-01 S6520 00 mtr-AAA-TR565 BC6520 04-02-01 X D6520 00-ABC 8951511 03-01-VTM X |
B2:
=LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X"} ) which you need to confirm with control+shift+enter (instead of just with enter) and then copy down. C2:C3 houses the search substrings of interest and A2 a target string. Note. Replace SEARCH with FIND if the evaluation must be case-sensitive. Tiziano wrote: I would like to be able to search and flag a list of string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear as indicated in Column B. Thanks in advance for your help. Column A Column B Column C ----------------- -------- ---------- A6520 04-02 X 6520 04-02 1511 03-01-VT X 1511 03-01 S6520 00 mtr-AAA-TR565 BC6520 04-02-01 X D6520 00-ABC 8951511 03-01-VTM X |
Your solution works great, Aladin, except for a tiny
detail... Say I am going to have a variable number of substrings in Column C and thus I do not want to include a range in terms of rows in the function, why does the following not work? =LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"}) Am I stuck with something like this? =LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{"", "X"}) "Aladin Akyurek" wrote in message ... B2: =LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X"} ) which you need to confirm with control+shift+enter (instead of just with enter) and then copy down. C2:C3 houses the search substrings of interest and A2 a target string. Note. Replace SEARCH with FIND if the evaluation must be case-sensitive. Tiziano wrote: I would like to be able to search and flag a list of string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear as indicated in Column B. Thanks in advance for your help. Column A Column B Column C ----------------- -------- ---------- A6520 04-02 X 6520 04-02 1511 03-01-VT X 1511 03-01 S6520 00 mtr-AAA-TR565 BC6520 04-02-01 X D6520 00-ABC 8951511 03-01-VTM X |
Formulas that operate on arrays like the one I suggested do not admit
whole columns as range references. However, we can do the following: E2: =MATCH(REPT("z",255),C:C) which calculates the position of the last in use in column C. The formula needs to be modified: =LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)),{0, 1},{"","X"}) Still to be confirmed with control+shift+enter. Tiziano wrote: Your solution works great, Aladin, except for a tiny detail... Say I am going to have a variable number of substrings in Column C and thus I do not want to include a range in terms of rows in the function, why does the following not work? =LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"}) Am I stuck with something like this? =LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{"", "X"}) "Aladin Akyurek" wrote in message ... B2: =LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X "}) which you need to confirm with control+shift+enter (instead of just with enter) and then copy down. C2:C3 houses the search substrings of interest and A2 a target string. Note. Replace SEARCH with FIND if the evaluation must be case-sensitive. Tiziano wrote: I would like to be able to search and flag a list of string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear as indicated in Column B. Thanks in advance for your help. Column A Column B Column C ----------------- -------- ---------- A6520 04-02 X 6520 04-02 1511 03-01-VT X 1511 03-01 S6520 00 mtr-AAA-TR565 BC6520 04-02-01 X D6520 00-ABC 8951511 03-01-VTM X |
Thank you again, Aladin!
While testing your formula, I came across a problem if one or more of the cells in column C are left blank while others are populated. (Say, for instance, I have data in cells C1, C2, C4, but not in C3. If this happens, then the formula puts an "X" in column B for every string mentioned in column A.) I've been thinking about a solution for this, but I just am not experienced enough to come up with something. Would you have a suggestion to this problem? Thanks. "Aladin Akyurek" wrote in message ... Formulas that operate on arrays like the one I suggested do not admit whole columns as range references. However, we can do the following: E2: =MATCH(REPT("z",255),C:C) which calculates the position of the last in use in column C. The formula needs to be modified: =LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)),{0, 1},{"","X"}) Still to be confirmed with control+shift+enter. Tiziano wrote: Your solution works great, Aladin, except for a tiny detail... Say I am going to have a variable number of substrings in Column C and thus I do not want to include a range in terms of rows in the function, why does the following not work? =LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"}) Am I stuck with something like this? =LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{"", "X"}) "Aladin Akyurek" wrote in message ... B2: =LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"","X "}) which you need to confirm with control+shift+enter (instead of just with enter) and then copy down. C2:C3 houses the search substrings of interest and A2 a target string. Note. Replace SEARCH with FIND if the evaluation must be case-sensitive. Tiziano wrote: I would like to be able to search and flag a list of string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear as indicated in Column B. Thanks in advance for your help. Column A Column B Column C ----------------- -------- ---------- A6520 04-02 X 6520 04-02 1511 03-01-VT X 1511 03-01 S6520 00 mtr-AAA-TR565 BC6520 04-02-01 X D6520 00-ABC 8951511 03-01-VTM X |
The right thing to do is not to have any empty cells in the range that
must house the substrings to search for. Tiziano wrote: Thank you again, Aladin! While testing your formula, I came across a problem if one or more of the cells in column C are left blank while others are populated. (Say, for instance, I have data in cells C1, C2, C4, but not in C3. If this happens, then the formula puts an "X" in column B for every string mentioned in column A.) I've been thinking about a solution for this, but I just am not experienced enough to come up with something. Would you have a suggestion to this problem? Thanks. "Aladin Akyurek" wrote in message ... Formulas that operate on arrays like the one I suggested do not admit whole columns as range references. However, we can do the following: E2: =MATCH(REPT("z",255),C:C) which calculates the position of the last in use in column C. The formula needs to be modified: =LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)),{ 0,1},{"","X"}) Still to be confirmed with control+shift+enter. Tiziano wrote: Your solution works great, Aladin, except for a tiny detail... Say I am going to have a variable number of substrings in Column C and thus I do not want to include a range in terms of rows in the function, why does the following not work? =LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"}) Am I stuck with something like this? =LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1},{ "","X"}) "Aladin Akyurek" wrote in message ... B2: =LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"", "X"}) which you need to confirm with control+shift+enter (instead of just with enter) and then copy down. C2:C3 houses the search substrings of interest and A2 a target string. Note. Replace SEARCH with FIND if the evaluation must be case-sensitive. Tiziano wrote: I would like to be able to search and flag a list of string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear as indicated in Column B. Thanks in advance for your help. Column A Column B Column C ----------------- -------- ---------- A6520 04-02 X 6520 04-02 1511 03-01-VT X 1511 03-01 S6520 00 mtr-AAA-TR565 BC6520 04-02-01 X D6520 00-ABC 8951511 03-01-VTM X |
I was thinking about linking the spreadsheet
that I am building with another existing one. The substrings in column C would come from this other spreadsheet. The only way that I know how to import data is by using a formula like this: =[other_spreadsheet.xls]Sheet1!$A$1 =[other_spreadsheet.xls]Sheet1!$A$2 =[other_spreadsheet.xls]Sheet1!$A$3 etc. The downside of this is that, if there are blank cells in col. A of other_spreadsheet.xls, they will also be imported... I will start another thread with more information about my wanting to import data from another spreadsheet as there are a few more details to mention and this is deviating from my original question. Thanks for your help!! "Aladin Akyurek" wrote in message ... The right thing to do is not to have any empty cells in the range that must house the substrings to search for. Tiziano wrote: Thank you again, Aladin! While testing your formula, I came across a problem if one or more of the cells in column C are left blank while others are populated. (Say, for instance, I have data in cells C1, C2, C4, but not in C3. If this happens, then the formula puts an "X" in column B for every string mentioned in column A.) I've been thinking about a solution for this, but I just am not experienced enough to come up with something. Would you have a suggestion to this problem? Thanks. "Aladin Akyurek" wrote in message ... Formulas that operate on arrays like the one I suggested do not admit whole columns as range references. However, we can do the following: E2: =MATCH(REPT("z",255),C:C) which calculates the position of the last in use in column C. The formula needs to be modified: =LOOKUP(COUNT(SEARCH($C$1:INDEX(C:C,$E$2),A1)), {0,1},{"","X"}) Still to be confirmed with control+shift+enter. Tiziano wrote: Your solution works great, Aladin, except for a tiny detail... Say I am going to have a variable number of substrings in Column C and thus I do not want to include a range in terms of rows in the function, why does the following not work? =LOOKUP(COUNT(SEARCH(C:C,A1)),{0,1},{"","X"} ) Am I stuck with something like this? =LOOKUP(COUNT(SEARCH($C$1:$C$65536,A1)),{0,1}, {"","X"}) "Aladin Akyurek" wrote in message ... B2: =LOOKUP(COUNT(SEARCH($C$2:$C$3,A2)),{0,1},{"" ,"X"}) which you need to confirm with control+shift+enter (instead of just with enter) and then copy down. C2:C3 houses the search substrings of interest and A2 a target string. Note. Replace SEARCH with FIND if the evaluation must be case- sensitive. Tiziano wrote: I would like to be able to search and flag a list of string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear as indicated in Column B. Thanks in advance for your help. Column A Column B Column C ----------------- -------- ---------- A6520 04-02 X 6520 04-02 1511 03-01-VT X 1511 03-01 S6520 00 mtr-AAA-TR565 BC6520 04-02-01 X D6520 00-ABC 8951511 03-01-VTM X |
All times are GMT +1. The time now is 04:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com