Remove certain characters from a string

1.1 Expected effect

Users sometimes need to clean out some unwanted characters in the data.

For example, remove the "[]" and the contents in between in the table, as shown below.

1.2 Implementation idea

First use the FIND function to find the position of "[" and "]" in the string, and then use the replace function to replace part of a text string with a null value according to the specified number of characters.

2.1 Create Self-Service dataset

Create a new Self-Service dataset and check the fields that need to be checked in, as shown in the following figure.

2.2 Add new column

Click "add", select New Column, and enter a name for the new column in the new column settings box that pops up in the system to add a formula, as shown in the following figure.

The formula uses two functions, details can be seen: text function

1）REPLACE

REPLACE(old_text,start_num,num_chars,new_text), you can specify the number of characters, according to the number of other text strings to replace a text string in part. Example: REPLACE("0123456789",5,4,"*")is equal to 0123*89, which will replace the 4-bit text string with * after the 5th character in 0123456456789.

2）FIND

FIND(find_text,within_text,start_num), starting at the specified index (start_num), return the first occurrence of the specified substring (find_text) in the index of this string (within_text).

Example: FIND("i","Information",1) equals 9 。

After clicking "OK", you can see that the new column "[]" and the contents in the middle are not displayed, as shown in the following figure.