Find and Replace

I’m looking to execute a find and replace function on a dataset with names in column A that are formatted in a “LAST/FIRST” format, where I’d like to replace the “/” with a ", " so that the output would be “LAST, FIRST”

Any advice?

Thanks

In String Manipulation node use replace function.

2 Likes

I have a similar question.
I want to use the String Manipulation node also for “Find and Replace”, but for a number of values

At the moment it looks like this:

replace($Marke$,“TC”,“Tch”);
replace($Marke$,“EDU”,“GvE”)

Some more should follow. But I can’t figure out how to close this statement.

I always get

Invalid settings
Unable to compile expression
ERROR at line 65
Unreachable code

Can anybody help me?

1 Like

You can try this kind of expression in Column Expression node.

Thank you for the prompt answer. I now installed Column Expressions, but it doesn’t seem to work. I wrote in the first expression

1 column(“Marke”)
2 replace(“TC”, “TC”,“Tch”)

and in the second expression

  1. column(“Marke”)
  2. replace(“EDU”, “EDU”,“GvE”)

What happens is, that he appends one column with “Tch” written everywhere in it and a second column with “GvE” written in all column fields. But what I wanted to achieve is, that he replaces the word “TC” with “Tch” and “EDU” with “GvE” in the column “Marke” with all other words in this column unchanged.

Use if construction like in example below:

I used this formula:

if (string((column(“Marke”),“TC”)))
{“Tch”}

, but the result is the same. For each expression, I got one additional column with the just the string mentioned in the formula. It does not replace the old string in the original column with the new one.

You need to use else part of if statement to submit other conditions
if (…) {…} else {
if (…) {…} else {
if (…) {…}
}
}

3 Likes

Thanks again for your help.
I’m still making a mistake. I typed

if (string((column(“Marke”),“TC”)))
{“Tch”}
else {column(“Marke”)}

and got the following error:

An error occurred during the execution of the script: Error occurred during the conversion of the result ‘Tch’ to ‘List’: java.lang.String cannot be cast to [Ljava.lang.Object;

What does this mean and what am I doing wrong? I have to admit, I’m quite a newbie to this and have no experience in Java or other programming languages.

if (column(“Marke”) ==“TC”) {“Tch”}
else {column(“Marke”)}
This have to work.

2 Likes

Thanks a lot. This works, but not in the way I need it.
First I encountered again the error:

An error occurred during the execution of the script: Error occurred during the conversion of the result ‘Tch’ to ‘List’: java.lang.String cannot be cast to [Ljava.lang.Object;

But when I unmarked “Collection”, it worked without any error.

However, what happens now is, that he creates a new column “Marke(#1)” in which “TC” is replaced correctly with “Tch” and the rest stays like it is. But then, it creates a second new column "Marke(#2) in which “EDU” is replaced correctly with “GvE” and the rest stays like it is. But what I need is just one new column, in which “TC” and “EDU” are replaced and the rest stays like it is.

If you post your WF I can fix it.

Okay, I found my mistake. I rewrote the formula to

if (column(“Marke”)==“TC”){“Tch”} else {
if (column(“Marke”)==“EDU”){“GvE”} else {
if (column(“Marke”)==“MEL”){“Mel”}
else {column(“Marke”)}
}
}

And it works perfectly. My mistake was, that I mistook the last two “}” in your reply three days ago as place holders.

So thanks a lot for your kind help!

1 Like

Great that you fix the mistake. Please, mark my answer as a solution.

1 Like

Could you please help me with this problem?
My column is named zip and some of the rows have less than 5 numbers and some have 0. I would like to change these values to Undefined. I tried this if statement and it didn’t work.
if (column(zip)= 0){“Undefined”}
else{column(zip)}
OR
if (column(“zip”)==0){“Undefined”}
else{column(“zip”)}
This column is numeric datatype but I went ahead and tried this anyway
OR
if (column(“zip”)=0){“Undefined”}
else{column(“zip”)}
OF
if (column(“zip”) == “0”){“Undefined”}
Apparently this one didn’t yell at me for error like the others above but said CANNOT CONVERT STRING TO LIST
Nothing works so far

I think instea of checking with “=” sign you should try .equals method for comparison
br

It is not working. It gives me error.

if (column(“zip”) == “0”){“Undefined”}
This one actually turned all the row value into ?