Search and Filter in a string

[annanmty]

I am working with an Excel table with about 7500 lines exported from a database. I do not have access to the database and need to filter the table to look up a numeric site location in one of the columns.

For example, I need to know which records are associated with site location 65. Because records can be associated with one or many sites, I need to find just 65 in a cell, not 165, 651, etc. Records with multiple associated site locations have those locations separated by commas. (60, 65, 68, 103, 104, 105, 108). A 65 could be the only site in a cell, the first site of many, or the last site of many.

This is the formula I tried in each row of K from K3 down:

=OR(ISNUMBER(SEARCH($K$2,I7)),(ISNUMBER(SEARCH($L$2,I7))))

In K2, I have ", 65" (no quotes). In L2, I currently have " 65," (no quotes). I am not capturing cells with just 65 as the only entry. Any ideas?

All help appreciated!

 

[annanmty]

I forgot to give details:

Excel 2010
Windows XP

 

[cgendron]

If you only need to filter to look at site 65, can't you just drop a filter on the top line and only choose 65 in column k?

 

[T. Valko]

I am working with an Excel table with about 7500 lines exported from a database. I do not have access to the database and need to filter the table to look up a numeric site location in one of the columns.

For example, I need to know which records are associated with site location 65. Because records can be associated with one or many sites, I need to find just 65 in a cell, not 165, 651, etc. Records with multiple associated site locations have those locations separated by commas. (60, 65, 68, 103, 104, 105, 108). A 65 could be the only site in a cell, the first site of many, or the last site of many.

This is the formula I tried in each row of K from K3 down:

=OR(ISNUMBER(SEARCH($K$2,I7)),(ISNUMBER(SEARCH($L$2,I7))))

In K2, I have ", 65" (no quotes). In L2, I currently have " 65," (no quotes). I am not capturing cells with just 65 as the only entry. Any ideas?

All help appreciated!

Try this...

=ISNUMBER(SEARCH(", 65",", "&A2))

 

[annanmty]

If you only need to filter to look at site 65, can't you just drop a filter on the top line and only choose 65 in column k?

I need to filter for every instance of 65, alone in a cell or as part of a series of numbers. I am trying to avoid manually going through all the cells looking for 65 and there is a limit to what the auto filter will show, limited by width of the dropdown and I believe to the first thousand records.

Over the course of the task I will have to filter the records for hundreds of site locations, so I am hoping for a more automated process.

 

[annanmty]

Try this...

=ISNUMBER(SEARCH(", 65",", "&A2))

Thank you! This seems to be working well. I changed the column reference from A to I to fit my example.

Is there a way to change the formula to refer to another cell for the site location number to search for? When I (or the others using this sheet) need to filter for other locations, it would be nice not to have to re-copy the formula down.

 

[T. Valko]

Thank you! This seems to be working well. I changed the column reference from A to I to fit my example.

Is there a way to change the formula to refer to another cell for the site location number to search for? When I (or the others using this sheet) need to filter for other locations, it would be nice not to have to re-copy the formula down.

Sure...

X1 = , 65

Then refer to that cell:

=ISNUMBER(SEARCH(X1,", "&A2))

 

[annanmty]

I realized I never posted my thank you for my add-on question. Thank you! -- I appreciate you helping make my work life easier.

 

[T. Valko]

I realized I never posted my thank you for my add-on question. Thank you! -- I appreciate you helping make my work life easier.

You're welcome. Thanks for the feedback!