Sunday, April 10, 2011

Select rows in excel based on criteria without FIND and nasty #VALUE errors

So I want to get rid of all fields in an Excel spreadsheet that contain the value "{". The way I would think to do something like this in any programming language would be to use an 'instr' type of function and delete records accordingly.

The problem with Excel is that you need to use a function like FIND. The problem with FIND is that it returns that nasty "#VALUE" if there is no such field.

I came across this solution on the _interwebs_ that is pretty nifty. The way it works is to get the length of a string where you have replaced (or substituted) the character "{" and compare that with a string that has not been replaced. If the string lengths are different, then boom... you can show/not show that field. I approve of this!

Here's an example:



No comments:

Post a Comment