Tuesday, March 08, 2011

OpenOffice find function returns error when nothing is found

I wanted to sort operations on my bank sheet. I thought I'd extract the value at the end of a row (contained in cell D4) according to the first word (the word "transfer" for example) in a cell at the begining of the row (Cell B4). So I used this formula
=IF(FIND("TRANSFER",B4),D4,"")
If the culumn B4 contains the word TRANFER, this will return the value of column D4
  • But the problem is that if the word is not present, then the function FIND returns an error #value!
  • The solution is to use the ISNUMBER() function, to avoid that error. The function becomes something of the sort:
=IF(ISNUMBER(FIND("TRANSFER",B4)),D4,"")
I found this solution on the OpenOffice wiki.

No comments: