EXCLUDE()

EXCLUDE() returns a variable length string, excluding characters that you specify from the result.

EXCLUDE() is the opposite of the INCLUDE() function.

Function Format

EXCLUDE(C1,C2)

EXCLUDE() returns a string containing the characters in C1, excluding any characters that appear in C2, in the same order that they appear in C1.

Examples

To remove all numbers from an address, specify:

EXCLUDE("123 any street","0123456789")=" any street"

To remove a comma, a forward slash and a number sign from the Prodno field, specify:

EXCLUDE(Prodno,",/#")

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:

EXCLUDE(Prodno,”1~6”)

EXCLUDE(ProdDesc,”a~mA~M”)

To specify the tilde character as one of the values, for example you want to exclude only the letters A, B, C and tilde, then specify back to back tilde characters:

EXCLUDE(ProdDesc,”ABC~~”)

The double tilde causes the tilde to be recognized as a unique value and not a separator of end-points in a range.

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.