Contains IF function

[grego9]

I am trying to add in a simple IF function that applies only if the cell contains a certain value. Does anyone know of a formula type that would return this - or what syntax I should use with the standard IF function if one is not available

thanks

[hariharanmca]

I am trying to add in a simple IF function that applies only if the cell contains a certain value. Does anyone know of a formula type that would return this - or what syntax I should use with the standard IF function if one is not available

thanks

it Should be

Condition
--------------------
If <Condition> Then
<Execute True Part>
Else
<Execute False Part>
End If


Function
------------------------
Dim strReturnValue as String
'Example is in string datatype you can use any VB datatype
strReturnValue = IIF(<Condition>, "Return True", "Return False")

[Killer42]

I am trying to add in a simple IF function that applies only if the cell contains a certain value. Does anyone know of a formula type that would return this - or what syntax I should use with the standard IF function if one is not available

Not entirely sure I understand the question. But what about nesting your IF functions. For example, let's say you want to do something only if cell A1 = "Q". You could do something like...

IF($A$1="Q",IF(Condition,TruePart,FalsePart),Null)

[hariharanmca]

Not entirely sure I understand the question. But what about nesting your IF functions. For example, let's say you want to do something only if cell A1 = "Q". You could do something like...

IF($A$1="Q",IF(Condition,TruePart,FalsePart),Null)

It would be iif and like
IIF($A$1="Q",IIF(Condition,TruePart,FalsePart),Nul l)

yes, we can work like this.

[kentgorrell]

'iif' is used in access whereas 'if' is used in excel for if you are referring to a cell in excel it would be if(A1=x,do this, do the other)
in access it is exactly the same except that you would use iif.

[hariharanmca]

'iif' is used in access whereas 'if' is used in excel for if you are referring to a cell in excel it would be if(A1=x,do this, do the other)
in access it is exactly the same except that you would use iif.

Of-Course, we can use IIf in Vb 6.0 too. How could there be any doubt.

[Killer42]

Of-Course, we can use IIf in Vb 6.0 too. How could there be any doubt.

I just took it that we were working in Excel, based on the references to "cell", "formula" and "standard IF function". It does tend to generate confusion though, when people omit relevant information such as the environment in which they are working.

Hint hint, grego9. :)

[grego9]

Apologies - we were working in Excel 2000. Thanks for the help - I'm not sure it has quite answered the question. My problem is trying to find the syntax if the cell contains any of the text in a particular string. So as an example i might be looking for a cell that contains GGG anywhere in it - so this would return the following cells:

CCGGG£
GGGDD
and
HHHGGG

So it's really the containing part that is key to the IF Function syntax for me

[Killer42]

Ah! So you want to know whether any part of the field matches your value. Let's see...

Well, according to the Excel doco (you really should have had a look there - I just looked up IF) there's a technique to "Check if part of a cell matches specific text, and return a value". this sounds like what you're after.

Use the IF, FIND, SEARCH and ISNUMBER functions to do this task.

Example:
=ISNUMBER(FIND("BD",A3)) Checks to see if A3 contains BD.

This will return True or False, so you can then use that as the input to your IF() test. In other words...

=IF(ISNUMBER(FIND(<search-for>,<where-to-search>)),TruePart,FalsePart)

[grego9]

brilliant thank-you - sorry I didn't reply earlier I have been on my hols!

[Killer42]

brilliant thank-you - sorry I didn't reply earlier I have been on my hols!

No problem. Glad we could help. :)