How-To Use Remove & Replace Formulas

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:

Remove

Parameters:

• 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

Replace

Parameters:

• 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.