Finding and replacing cell values
The [highlight txt=’find and replace’] instruction allows a user to find all cells which are of or contain a particular value, and replace this value with a new value. The instruction can be configured to be case sensitive. The replacement value can be configured by the user to be either a number, a text string, a date, currency value, or a Boolean value of their choice.[message type=”warning”]For the purposes of this article we are going to use a simple and fictitious Excel file. It has already been uploaded into a data step. You will want to replace this file with one of your own.[/message]
Before we get started
Please further create a ‘Manipulate a file’ or ‘Multiple file manipulations’ action step so that you can add and use instructions.
The target file that we are going to use looks like this:
1. Create a ‘Find and replace’ instruction.
2. Link the file named “Target” as the target file.
3. Select Sheet1.
4. Use an Advanced Range from A1 to the last cell in ColumnA as far as D.
5. Type “ABC” into the ‘Find value’ field. This is the value that the instruction will search for.
6. Tick the ‘Search within cell?’ box. This enables the instruction to search for the specified value within the values contained by the cells in the selected range.
7. The first condition of the ‘Replace value with’ rule should be selected to “Literal” by default. Alternatively, the replacement value can be configured to be a value which is contained in a data property elsewhere in the process, that is, if the “Input/Output of another step” option is selected.
8. Select ‘String’ for the second condition of the ‘Replace value with’ rule.
9. Type “XYZ” into the field above ‘String’. This specifies that the replacement value will be the text string of “XYZ”.
10. Save and run the step.
11. After the step finishes running, download and open the target file: