IF formulas working that doesn't work when joining them using IF(OR formulas, help please :)

[EricB]

Hello everyone,

I’m sure this is an easy one but well, hard enough for me it seems :winking_face:

I have 2 working IF formulas, that run correctly independently but I need to run them together using IF(OR formula and it doesn’t work the way I do it.
Thanks in advance for your help, surely an easy one for many of you guys.

2 working IF formulas:

IF(LEN({Langue Maternelle})-LEN(SUBSTITUTE({Langue Maternelle},"Français",", "))>LEN("Français"),"Yes","No")

IF(LEN(Langues)-LEN(SUBSTITUTE(Langues,“Français”,", "))>LEN(“Français”),“Yes”,“No”)

Non-working combine IF(OR formula:

IF(OR(LEN(Langues)-LEN(SUBSTITUTE(Langues,"Français",", "))>LEN("Français"),(LEN({Langue Maternelle})-LEN(SUBSTITUTE({Langue Maternelle},"Français",", "))>LEN("Français")),"Yes","No"))

Airtable accepts it but it returns blank cells everywhere instead of “yes” or “no”.

Thanks again !
Eric

[augmented]

Hi Eric. It appears to me that at least one closing paren is in the wrong place. Your “Yes” and “No” are inside the OR() function. Since they are supposed to be part of the IF(), they should only have one closing paren to the right of them. So, I would take one of the last two parens and move it inside the comma right before the “Yes”.

Make sense?

[Alexey_Gusev]

Hi,

suppose you are checking for 2 or more values of “Français” in field 1 OR in field 2
i would suggest to write such formulas in Notepad etc, and then copy to formula field. No need to worry about extra spaces and line breaks, just put “as is”
also, it’s a smart way for one field, but a bit robust when scaling for more.
you may substitute single occurence in each of fields and then check the rest

IF(
FIND(“Français”,
SUBSTITUTE({Langue Maternelle},“Français”,", “,1)&
SUBSTITUTE(Langues,“Français”,”, ",1)
)>0,“Yes”,“No”)

[EricB]

Thanks @Alexey_Gusev, sounds like a good idea but doesn’t seem to work here.

[Alexey_Gusev]

Hi,

it’s because forum engine formats double quotes in a strange way. When i posted it from notepad text file, it has usual “symmetric” double quotes.
Let’s try single quote and other type of text quoting, ‘Preformatted text’. I checked and it should work.

IF(
FIND('Français',
SUBSTITUTE({Langue Maternelle},'Français',', ',1)&
SUBSTITUTE(Langues,'Français',', ',1)
)>0,'Yes','No')

by the way,
why you are using SUBSTITUTE “Français”, with ", " ?
instead of substituting it with empty string?
In your case it has no difference, but for other purposes, you may apply such way to detect the number of occurences in the text (with correct quotes):

(LEN(Langues)-LEN(SUBSTITUTE(Langues, “Français”, “”))/LEN(“Français”)