Replace any character string with exception

[msantoso]

Hello 

I need your help for this silly simple question, but I can't find it out. 

in this column, I have 3 type of values : 

- "No" 

- null values

- columns with various character strings

I need to replace the columns with various character strings with "yes"

thank you for your help

Myriam

[jasperlch]

Hi @msantoso,

You could put the following formula:

if Field_1 = 'No' or IsNull(Field_1) then Field_1 else 'Yes' endif

Thanks,

Jasper

[msantoso]

Hi JasperIch

thanks for this solution

here is the formula 

IF [COLLATERALS]='No' OR isNull([COLLATERALS])
THEN [COLLATERALS] ELSE 'Yes' ENDIF

the Null values are replaced with a yes, which is not what I expected. Any idea why the formula puts a 'Yes' to empty cells? 

thanks

Myriam

[jasperlch · Accepted solution]

Hi @msantoso

Would you try:

IF [COLLATERALS]='No' OR isEmpty([COLLATERALS])
THEN [COLLATERALS] ELSE 'Yes' ENDIF

isEmpty() function includes cells with an empty string (e.g. "") and cells with white space only.

Thanks,

Jasper

[msantoso]

Hi 

It works fine, thank you! 

good to remember the difference btw isEmpty and isNull.

thanks again

Myriam