match doesn't work in cells with lots of data?

[sithchick]

Hi all!

I have a column of cells that each contain identifier codes, sometimes many identifier codes in the same cell. They are separated by :: (for example, AHD4::EJ7H::9EIO::etc). Based on a separate list, I am using match to determine the row location of cells matching my new list. For example, I am looking for AHD4 and 9EIO; I am using match to determine that they are in row 15. To deal with the :: issue, I am using:

Please Login or Register  to view this content.

My problem is that while match works sometimes, it doesn't work all the time. It usually correctly returns the row number, but sometimes throws #n/a even when I can use the ctrl-f find function and verify that, indeed, the value I am looking for exists in that column. Thus far, the only common thread for instances in which this error occurs is that the query identifier is in a cell with lots of other query identifiers (at least 500, meaning that there are +4000 characters in the cell, sometimes 2500). All of the cells are the same format and I can't think of anything else that these error instances have in common.

I would appreciate any thoughts or insight on fixing this problem (or identifying what the problem is, if it isn't the number of characters in the cell)-- I am working with a small dataset now, so it is tedious to use ctrl-f but not too terrible, but I expect to be working with larger datasets soon. Thanks!

[FDibbins]

take 1 of those that you think it should find, and remove most of the extraneous text either side - but still leaving plenty both sides - and see if the results are the same.

Alternatively, upload a SMALL sample of what you are working with, so we can take a look