[Solved] IF a field contains ref2 or ref2 or ref1+ref2

Discuss the spreadsheet application

[Solved] IF a field contains ref2 or ref2 or ref1+ref2

Postby ldkemp » Tue Jun 24, 2008 6:01 pm

Hello,

I was hoping someone could help.

I am trying to create a function that will assign a value depending on if the word; dimond, gold or diamond and gold appear anywhere in a field.

I have tried =IF(FIND("diamond";D25);"Diamond";IF(FIND("gold";D25);"Gold";0))
But this only works for the first find function (diamond) and does not return any result for the second find function (gold)

Thank you for taking the time to read the post and if you can help I would be very greatful

Regards Liam
Last edited by Hagar Delest on Wed Jun 25, 2008 12:05 pm, edited 1 time in total.
Reason: tagged the thread as Solved.
OOo 2.4.X on Ms Windows XP
ldkemp
 
Posts: 5
Joined: Tue Jun 24, 2008 2:23 pm

Re: IF a feild contains ref2 or ref2 or ref1+ref2 anywhere

Postby acknak » Tue Jun 24, 2008 6:24 pm

The FIND function produces an error if there is no match. Once any part of a formula results in an error, the whole formula result is an error.

You have to explicitly test for an error result using something like =IF(ISERR(FIND(...
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: IF a feild contains ref2 or ref2 or ref1+ref2 anywhere

Postby ldkemp » Tue Jun 24, 2008 6:36 pm

I get error Err:508
OOo 2.4.X on Ms Windows XP
ldkemp
 
Posts: 5
Joined: Tue Jun 24, 2008 2:23 pm

Re: IF a feild contains ref2 or ref2 or ref1+ref2 anywhere

Postby Villeroy » Tue Jun 24, 2008 6:48 pm

ldkemp wrote:I get error Err:508

Which is, according to the help files, a bracketing error. Resolve it. The function wizard is a useful tool (Ctrl+F2).
Code: Select all   Expand viewCollapse view
=IF(isnumber(FIND("diamond";D25));"Diamond";IF(isnumber(FIND("gold";D25));"Gold";0))

I suggest without testing.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: IF a feild contains ref2 or ref2 or ref1+ref2 anywhere

Postby ldkemp » Wed Jun 25, 2008 11:24 am

Thank you very much, that has worked a charm.

Regards Liam
OOo 2.4.X on Ms Windows XP
ldkemp
 
Posts: 5
Joined: Tue Jun 24, 2008 2:23 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 25 guests