The find replace tool is similar to a V lookup in Excel, the tool takes a value looks it up in a reference table and returns an associated value. Referring to the illustration on the right, we take the department ID from the employee file, look it up in the department file, and return the department name, which you can see in the third picture. Let's start a new workflow by importing our 3.4 find replace spreadsheets. So we'll go to the Insert tab, drag in our two input data tools and connect to the 3.4 employee file and the 3.4. DEP file will then go to the join tab and drag in our find replace tool and the top anchor refers to the final data set or employee and the bottom anchor is the replaced Tata set or department. So let's drag department into the Replace.
And in the configuration pane on the left we have our Find and Replace section. In find, we have the options to find the beginning of the field, any part of the field or entire field. Beginning means that it will find matching department IDs at the start of the field. So the entire field doesn't have to contain what's being searched for any part of the field will look through the entire value for department ID. And if it finds a match at the left, middle or right part of the value, it will do a Find and Replace. And entire field which is what we'll be using means the department ID must match entirely.
For find within field we'll select from our find or employee data set department ID. However, it's not appearing in our list. have values to choose from. Because department ID is listed as a number in our data set, whilst Find and Replace only works with text. So what we'll do first is go to preparation and drag in a select tool here and a select tool here. And let's put our department ID as a string, and our department ID here as a string.
If we go back here, we can then now select department ID, from our employee file, and department ID within the find value. So what's happening here is that we're looking up the department ID within the employee file, finding that department ID in the department file. And once it's found that we're going to replace the department ID with the department name Let's add a browse tool to the end of our workflow with Ctrl Shift B and run our workflow with Ctrl R. And here we have our department ID column replaced with department name. If we go back to the find replace tool and select append, and tick department name and run our workflow. Here we have our department name which has been included at the end of our employee data set.