[Solved] How to avoid #VALUE!

[jnoake]

Calc - FIND function, if FIND does not 'find' the text, it returns #VALUE!
My first reaction is 'Why not 0???' and then I find it very difficult to locate any advice on how to deal with #VALUE! - since google etc. strip off the # and ! and return millions of hits for rubbish.
I have searched the OpenOffice forum, wiki's and FAQ and I am stunned to see that even the word 'value' returns NO hits?!
Even the help does not seem to mention anything that could be done with #VALUE!

It could be simpler - what I want to do is filter a list (bank statement) for all lines that 'contain' the word "ASDA" (note:not 'equal to' or '=' , since each instance also has a date and location embedded in the text like this:
7206 19APR09 0000 , ASDA SUPERSTORE , RUNCORN
and so varies) - this seems not to be available so I started looking at pasting a column with a formula that would go like :=if(FIND("ASDA";C1)<>0;D1;"") but the #VALUE! result screws it up!.

Any ideas?

[ken johnson]

Try...

Code: Select all

=IF(ISNUMBER(FIND("ASDA";C1));D1;"")

Ken Johnson

[jnoake]

Brilliant - Thank you Ken Johnson.

[practicalcode]

Much thanks to the Aussie for solving the same problem I had!!! Very good tech skills down under from all I've worked with (thinking of moving there someday even...) But I digress...

Ok, that was a great help; however, we should add the little ISNUMBER() trick to the Documentation so that we don't have to then search for it, yes?...

So does anyone know the process for adding it to the following items:

Documentation/How Tos/Calc: FIND function
http://wiki.services.openoffice.org/wik ... D_function
Documentation/How Tos/Calc: SEARCH function
http://wiki.services.openoffice.org/wik ... H_function

???

Thanks in advance!

[Zizi64]

http://wiki.services.openoffice.org/wik ... R_function

Fact:
The WIKI documentation pages contain the description of the ISNUMBER() function.

My opinion:
The ISNUMBER is an individual function. It is not related to FIND() or SEARCH() functions.

Use it, when you need it...
It is impossible to create working examples for every combinations of calc functions...

[practicalcode]

I must admit, I disagree... While ISNUMBER() may have other uses, it is crucial to using FIND() & SEARCH() in any meaningful way where the string searched is not 'guaranteed' to contain the substring.

While I agree that it is impossible to create working examples for 'every' combination... It is very likely that one of two combinations will result for the use of FIND() & SEARCH()

1. It exists & results in a number
2. It doesn't exist & gives the #VALUE! error

The second case (which has a relatively high chance of occurring) is not represented at all... and I would say it's not 'obvious' how to deal with (or 'trap') the #VALUE! error ... to be honest, it's amazing to me that ISNUMBER() even works at all when passed an 'error' and doesn't itself return an error. <And in ISNUMBER()'s description, it doesn't mention that it could handle being 'passed' an error as an argument and being able to 'gracefully' handle such an occurance. For instance if you use IF() and as the conditional use the FIND() or SEARCH() as the 'conditional argument' it will return #VALUE! error if FIND() or SEARCH() propagate said error, and not gracefully handle it.> After all, you may think you need to use ISERROR() instead, which would not be as elegant as ISNUMBER() in this instance.

In summary, I still feel it should be added to the documentation, for it is a critical 'case' that is likely to occur... Furthermore, I also feel the ISNUMBER() documentation should be amended as well to include it's ability to gracefully handle / trap an error as an argument. I was hoping someone on here might know whom to contact... (if it's not an automated form, pls feel free to e-mail me the persons contact info / email & I'll take care of the work.)


((PS Pls do not necessarily focus on my use of 'trapping' an error... spirit, not letter for this brief post pls. ))

((PPS I will admit however, if I were defining the function, my vote would be for it to return 0 and not #VALUE!. I think #VALUE! is misused in this instance. All of the arguments were legal, the substring simply did not exist. ))

[practicalcode]

Wow... apparently all it takes is a registration.... I can edit it myself... awesome.

Therefore, I registered and updated the following links:
Documentation/How Tos/Calc: FIND function
http://wiki.services.openoffice.org/wik ... D_function
Documentation/How Tos/Calc: SEARCH function
http://wiki.services.openoffice.org/wik ... H_function

Though I may be ignored, I'd recommend that if someone has a problem (& then finds the answer) and it's an omission in the 'help' documentation, pls take the time to add the clarification... (Google may not always be there for us The registration process was painless, and it will help everyone.