Removing contents of one column from another

[mac95]

Hi,

For each row of data, I’d like to remove the contents of the cell in column A that appear in the cell of column B.

Before:

After:

This would have the same result as using the SUBSTITUTE function in Excel.

I’ve tried a couple of things in String Manipulation that haven’t quite worked:

• replace($Column B$, $Column A$, "") results in empty cells;
• removeChars($Column B$, $Column A$) removes any shared characters, not strings. (This results in “Star Trek” becoming “Tek”.)

What should I try instead?

Cheers!

[ArjenEX]

Hi @mac95

Could it be that your data is a bit dirty with trailling spaces etc.? Because replace($column2$,$column1$,"") is the correct syntax in String Manipulation to get the desired result.

[mac95]

Agh, I’m kicking myself now! replace is what I should have been working with, not replaceChars. Cheers!