Thread: Filtering as text? Is this formula as good as it can be?

Ok, so I have this massive text filter that I use to clean up the names of products in inventory feeds so that the names that the user sees on my site are what they would recognize. Instead of "Sr15 12/pk" it would be converted to "Morgan Hills Syrah". This filter checks each entry against a dictionary that, if there is a match, it'll replace the input with the correct output...or delete it all together.

It works for about 99% of the data I put through it, but there are some things that it just can't seem to catch. I'll put a certain phrase in the dictionary so that when it sees it, it should be deleted, but it doesn't.

Asterisks and parentheses seem tricky.

I didn't build it, but I think this is the formula from the step where it checks input against the dictionary. Full refers to fact that it's checking all the text in the input cell to a phrase dictionary.

=IF(ISERROR(VLOOKUP(G2, FULL!B:C, 2, FALSE)),G2,IF(VLOOKUP(G2, FULL!B:C, 2, FALSE)=0,"",VLOOKUP(G2, FULL!B:C, 2, FALSE)))

This is the first step in the "Segment" dictionary where it compares each word to a list of auto-deletes or replacements.

=IF(ISNA(VLOOKUP(I2,SEGMENT!A:B,2,FALSE)),I2,IF(VLOOKUP(I2,SEGMENT!A:B,2,FALSE)=0,"",VLOOKUP(I2,SEGM ENT!A:B,2,FALSE)))

When I run the filter, I'm looking at about 35,000 lines of data at a time, running it through 20,000 full dictionary filters and 4,000 segment filters...and there's 500 or so that keep coming through uncorrected.

I was told that since Excel is looking for numbers/math, that could be messing it up. Is there a way to have it look at everything literally? I want it to correct for exactly what I set up.

Thanks!

Eric