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

sample MS.xlsx

Transformation

Preparation

Accepted answers

jasperlch

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

All comments

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

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

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335