Whether you are extracting data from the screen, or directly from a database, the time will come when additional formatting is necessary. Foxtrot is equipped with hundreds of formulas to deal with such scenarios. We will review the Remove & Replace Formulas in this article. Sometimes these Formulas may be interchangeable. The example below will explain further:
- OriginalString: The expression to modify – required
- RemoveAlpha: True or False – Indicating whether alpha characters should be removed – optional
- RemoveNumbers: True or False – Indicating whether numeric characters should be removed – optional
- RemoveSpaces: True or False – Indicating whether spaces should be removed – optional
- RemoveAllOther: True or False – Indicating whether all non-alphanumeric characters should be removed – optional
- Keep: A list of characters to keep – For Example, if it is necessary to keep only square brackets, RemoveAllOther should be set to True, and Keep should contain  – optional
- Expression: The expression to modify – required
- Find: A value to be searched for within the Expression above – required
- ReplaceWith: The replacement value for the value found within the Find parameter – required
- Start: The position where the search is to begin (Default is 1 i.e. the first character) – optional
- Count: The number of substitutions to perform. The default value is -1, meaning that all possible substitutions will be made – optional
- Compare: The numeric value indicating the comparison type: 0 for binary, 1 for textual (Default) – optional
Choosing the right Formula…
As stated, these Formulas may often be interchangeable. For example, you may want to Remove all instances of the “$” character in a Variable. In this case, performing a Formula to RemoveAllOther would be the equivalent of a Replace Formula, in which replaces “$” with an empty value (i.e. nothing).
To determine which Formula works best for you, ask yourself what exactly do you need to do? For simplicity purposes, we will evaluate a specific example for each Formula.
Example 1 : Formatting Dates – Remove Method
You are attempting to save a file with the current date in mm/dd/yyyy format. Windows will not allow certain characters in a file name to avoid a conflict. Thus, we must Remove all forward slashes (/) from our Variable.
Notice that by setting the RemoveAllOther parameter to True, the result is now a 8-digit number
Example 2: Formatting Dates – Replace Method
Similar to the previous example, a date must be formatted to remove forward slashes. This time, however, instead of completely removing these characters, we wish to Replace them with a ‘-‘ character.
By Finding all instances of the / character and Replacing them with a -, my date now appears as mm-dd-yyyy.