How to Remove a Group of Characters From Text

Goal

I need to clean up a text column and want to remove specific characters from that data set.

Learn

Clean up numbers

If I have a telephone number like „+49 (0)345 2795-0322“ the result should be „49034527950322“. To achieve this goal use:

=REPLACEALL("+49 (0)345 2795-0322"; "(+| |\\(|\\)|-)")
=REPLACEALL(#Telephonebook!Numbers; "[+ \\(\\)-]")

Remove line breaks

In some case I want to remove all line breaks from at text object. E.g. if I have a text like

Hello
World. With a lot of 
line breaks“

the result should be

"Hello World. With a lot of line breaks". 

This can be done with:

=REPLACEALL(#TextInput;"(\\n|\\r)";"")

Remove special characters

If it is necessary to remove characters from a text which are from a special character set like UTF32, I like to define the character directly.

=REPLACEALL(#TextInput;CHAR(8734))

Workaround language specific formatting

Sometimes the string is a dollar amount which includes a leading "$" sign a "," separator and a ".00" ending.

First remove the leading currency sign "$", then the separator "," and lastly the trailing decimal point and zeroes ".0". This approach will work for different localisations, whereby it will be necessary to adjust the currency symbol, separator and decimal sign.

=REPLACEALL(#TextInput;"(\\$|,|\\.0*$)")

If you need to keep only alphabets (a~z, A~Z, 0~9, " ") you can define the group as follow.

=REPLACEALL(#TextInput;"[^a-zA-Z0-9\" \"]";"")

Remove HTML tags

To remove all HTML tags from a string use:

=REPLACEALL(#TextInputAsHTML;"<[^>]*>";" ")

Remove leading zeroes

To remove all leading zeroes from a string use:

=REPLACEALL(#<ColumnName>;"^0+";"")