Removing Certain Characters in a String

Expected Effect

Sometimes, you need to remove unnecessary characters from the data.

For example, you can remove the square brackets ([]) and the content inside them from a table, as shown in the following figure.

Implementation Method

First, you can use the FIND function to locate the positions of "[" and "]" within the string, then use the REPLACE function to substitute a specified portion of the string with empty values based on the determined character positions.

You can download the example data Contract Table.xlsx

Creating an Analysis Subject

Create an analysis subject and upload the example data with all fields selected by default, as shown in the following figure.

Adding a Column

Click Formula Column, set Name of Added Formula Column to Ranking One, enter the formula REPLACE(Ranking,FIND("[",Ranking,1),(FIND("]",Ranking,1)-FIND("[",Ranking,1)+1),""), and click OK, as shown in the following figure.

Two functions are used in this formula. For details, see Overview of Text Functions.

Formula description

1. REPLACE

You can use REPLACE(old_text, start_num, num_chars, new_text) to replace a specified number of characters in a string with new characters.

For example, REPLACE("0123456789",5,4,"*") returns 0123*89, replacing the 4-character substring starting at position 5 in 0123456789 with *.

2: FIND

You can use FIND(find_text, within_text, start_num) to return the index at which the specified substring (find_text) first appears within the string (within_text), starting from the specified index (start_num).

For example, FIND("i","Information") returns 9.

After you click OK, the square brackets ([]) and the content inside them in the new column are not displayed, as shown in the following figure.