REMOVE() takes out unwanted characters from a character string or field value.
Note: This function has been superseded by the functions INCLUDE() and EXCLUDE() but is still available for backwards compatibility. For more information on including and excluding characters in a string, see INCLUDE() and EXCLUDE().
Use REMOVE() for normalizing data fields (for comparison, sorting or joining) that do not have a consistent format, such as address fields. This function is also useful to remove punctuation or other invalid information from fields which have been poorly edited or have had no input edits applied.
REMOVE(C1,C2)
The REMOVE() function removes unwanted characters from string C1, retaining only the specified characters C2. This is similar in intent to CLEAN(), but its operation is very different. CLEAN() fills the remainder of a field with blanks, whereas REMOVE() moves any valid characters to the left side of the field, retaining their order and adding blanks to the end result so the length is the same as C1.
REMOVE("ABC 123 XX4", "ABC123") = "ABC123 "
REMOVE("zABC 123 XX4", "ABCX123") = "ABC123XX "
REMOVE("ABC 123 XX4", "1234567890") = "1234 "
To specify a long range of consecutive values (numbers or letters) without specifying all values, specify the first and last values separated by a tilde character:
REMOVE("ABC 123 XX4",”1~4”) = “1234 “
To specify the tilde character as one of the values, you must specify back to back tilde characters.
Note: Interpretation errors include situations like “z~a”, “9~1”, “~9” or “a~z~1”. In these cases, the string is read as individual characters and not as a range.