Find Replace Tool
Use Find Replace to find a string in 1 column of a dataset and look up and replace it with another string. You can also use Find Replace to append columns to a row.
Tip
This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.
Tool Components
The Find Replace tool has 3 anchors:
F input anchor: This input is the initial input table ("F" for "Find"). This is the table that is updated in the tool's results.
R input anchor: (Optional) This input is the lookup table ("R" for "Replace"). This is the table that contains data used to replace data in (or append data to) the initial input.
Output anchor: The output anchor displays the results of the Find Replace tool.
Configure the Tool
The Find Replace tool has 2 configuration modes depending on which anchors you use...
Manual Lookup Table: To manually enter a lookup table, you only need to connect to the F input anchor. In this case, the Find Replace tool configuration window has 3 sections: Lookup Table, Find, and Replace.
Dynamic Lookup Table: To dynamically use a lookup table, connect replacement data to the R input anchor. In this case, the Find Replace tool configuration window has 2 sections: Find and Replace.
Manual Lookup Table Configuration
Lookup Table Section
Enter the data that you want to find in the Find Values column. Then, in the Replace Values column, enter the data you want to use to replace the found values with.
Find Section
Column to Search: Select the column with data that you want to replace.
Choose the search criteria...
Contains: This search function searches for the instance of the column value in any part of the column. The entire column does not have to contain only what is being searched for.
Starts With: Searches for the instance of the column value at the beginning of the column. The entire column does not have to only contain what is being searched for.
Exactly Equal To: Searches for the instance of the column value contained within the entire column. The instance has to be there in its entirety to be replaced with the new value.
Select optional search conditions...
Case Sensitive: This option includes the case in the search.
Match Whole Word Only: Strings are only matched if there are leading and trailing spaces. For strings at the beginning or end of a cell, there must be a space at the other end.
Replace Section
You can choose to replace data using these options...
Replace Found Text with 'Replace Values'
Choose this option to replace the found text with what you've entered in the Lookup Table Replace Value column.
Optionally select Replace All Matched Items. Only use this if you selected Contains from the Search Criteria option.
Append Columns from Lookup Table
Choose this option to create a column populated with the Lookup Table data whenever the selected Find Values data is found within the specified Replace Values.
Check the columns you want to create.
Dynamic Lookup Table Configuration
Find Section
Search Criteria: Choose the search criteria...
Contains: This search function searches for the instance of the column value in any part of the column. The entire column does not have to contain only what is being searched for.
Starts With: Searches for the instance of the column value at the beginning of the column. The entire column does not have to only contain what is being searched for.
Exactly Equal To: Searches for the instance of the column value contained within the entire column. The instance has to be there in its entirety to be replaced with the new value.
Column to Search: Select the column from the F input anchor with the data that you want to replace.
Find Value: Select the column from the R input anchor that contains the data you want to search for in the Column to Search.
Select optional search conditions...
Case Sensitive: This option includes the case in the search.
Match Whole Word Only: Strings are only matched if there are leading and trailing spaces. For strings at the beginning or end of a cell, there must be a space at the other end.
Replace Section
You can choose to replace data using these options...
Replace Found Text
Choose this option to replace the found text with the text in the Replace Value column (from the R input anchor).
Optionally select Replace All Matched Items. Only use this if you selected Contains from the Search Criteria option.
Create Columns with Replace Data
Choose this option to create a column populated with the lookup table (R input anchor) data whenever the selected Find Values data is found within the selected Column to Search.
Check the columns you want to create.