Replace existing strings with new text in other columns

Hi there,

In the table below, I'm tring to replace existing strings in Column A and B with text in Column C and D.

There are 3 cases in the table:

1. Column A and B have null values, I want to replace them with Column C and D texts.

2. Column A and B have strings, I want to replace them with Column C and D texts.

3. Column A and B have strings, but null values in Column C and D, I want to keep existing strings in Column A and B.

So for case 1 I have successfully used the Isnull function to replace the null values in Column A and B, and for case 3 I don't need to do anything.

But for Case 2, I have no idea how to use the replace function or any other tools. Appreciate if you can help on this.

Expression

Accepted answers

Peachyco

I think the more straightforward test is if C/D has a value: If C/D is not null, perform the swap, else A/B keeps its value. This should cover all three cases.

Like this (for Column A):

IF !IsNull([Column C]) THEN [Column C] ELSE [Column A] ENDIF

All comments

SzymonDK

From what you described here, I would simply use the NOT operator before the IsNull() function. If it's not null then it contains a string, therefore we should replace it.
Something like this:

nullsolution.yxzp

Peachyco

I think the more straightforward test is if C/D has a value: If C/D is not null, perform the swap, else A/B keeps its value. This should cover all three cases.

Like this (for Column A):

IF !IsNull([Column C]) THEN [Column C] ELSE [Column A] ENDIF

louyang

Thank you so much guys for offering the logic behind and the formulas @SzymonDK @Peachyco !

I have taken Peachyco's method as it is more straightforward and I just have to switch the column names in the formula and it worked! Sometimes just need to think the other way.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703