Do you have part of a phrase in a column and you just need to replace one word? What if you wanted to add another word in that same cell?
In the example data below I need to add the word Comics to the cell containing Batgirl DC and I need to add Marvel Comics to both of the cells with Xmen characters.
How do I do this? With the Find Replace Tool!
First I drag a Text Input Tool with the characters I want to replace in a Word column. Then I type in the words I want to replace it with in a Replacement column.
Next I drag the Find Replace Tool to the canvas, attaching the first dataset to the F input anchor and then I connect the replacement dataset to the R replacement anchor.
As you see in the image below Alteryx identified that I want to search the column Character and I want to find the value from the column Word in my second data set. It also selected the Replacement column for the column containing the replacement.
Notice at the top you can set this tool to search for the Beginning of a field, Any Part of the Field or the Entire Field. For this example Any Part of the Field will suffice.
I don't need to check Case Insensitive Find since it appears the cases are the same.
I also don't need to check Match Whole Word Only.
The output appears as below with these options selected;
I could select the option to Append Field to Record to add the replacement as a new column. The results would appear as below if I selected that option;
In the next example I want to replace the beginning of the cell with a replacement. What if I wanted to remove the word The in from of Green Lantern and I wanted to change BATMAN to title case Batman.
I would add another Text Input Tool with the following;
I put nothing in the Replacement column for The.
I add my Find Replace Tool with the following configuration;
Notice I am simply changing the radio button to Beginning of Field.
My results are shown as below;
Oh no! There is a leading space now in front of Green Lantern. I simply go back to the The in the second data set and add a space after it.
Please note: There may be instances where you want to use the Find Replace Tool instead of using a Join Tool. If you are only bringing in one column, it may be faster to go this route instead.
In the example below, I have two data sets;
I want to bring in the Issue column into the final dataset, next to the Comic Company column. I could join the two data sets with a Join Tool on the Characters = Field 1 columns.
Or I could add the Find Replace Tool and configure it like below;
My output will appear as below and the workflow will be much faster!
As you can see in the Results log it went from .3 seconds to .2 seconds by using this tool!