Truncating the Content After Specified Characters Appearing Multiple Times

1.1 Expected effect

Users sometimes need to intercept the character at a specified position of a character that appears multiple times in a field.

For example, the user has characters with different lengths and needs to intercept the characters after the last "_", as shown in the following figure:

Or the user needs to intercept the value of column "B" in the "A|B|C" field. For example, the user needs to intercept all academic information, as shown in the following figure:

1.2 Implementation ideas 

Use the combination of INDEXOF, FIND, MID, SUBSTITUTE, RIGHT and other functions to implement the interception field.

Sample data: intercept the content after multiple occurrences of characters.xlsx

Upload sample data to FineBI, as shown in the figure below:

2.1 Create a self-service dataset

Create a self-service dataset, select the uploaded Excel dataset, and check the sample fields, as shown in the figure below:

2.2 Intercept specified characters

Click "+" to add "New column", as shown in the figure below:

2.2.1 Method One

Name it "Intercept the characters after the last _", enter the formula: INDEXOF(SPLIT(Field,"_"),LEN(SPLIT(Field,"_"))-1), click "OK", as shown in the following figure:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Formula description:

2.2.2 Method two

Add a new column, calculate the "calculated field according to the number of characters divided by _", enter the formula:LEN(SPLIT(Field,"_")), for example, the calculation result of "a_b_c" is 3, as shown in the following figure:

Add a new column to replace the last "_" with "-", enter the formula: SUBSTITUTE(Field,"_","-",LEN(SPLIT(Field,"_"))-1), as shown in the figure below:

Add a new column, start from the first character to find the position of "-", enter the formula: FIND("-",SUBSTITUTE(Field,"_","-",LEN(SPLIT(Field,"_"))-1 ),1),

Add a new column, calculate the number of characters after the last "_", enter the formula:LEN(Field)-FIND("-",SUBSTITUTE(Field,"_","-",LEN(SPLIT(Field,"_"))-1),1), as shown in the figure below:

Add a new column, extract the characters after the last "_" in the field, enter the formula RIGHT(Field,LEN(Field)-FIND("-",SUBSTITUTE(Field,"_","-",LEN(SPLIT(Field," _"))-1),1)), click "OK", as shown in the figure below:

2.3 Effect display

See section 1.1 of this article for details.

Note: The length of each data field in column B is inconsistent.

Sample data: Recruitment information.xlsx

Upload sample data to FineBI, as shown in the figure below:

3.1 Create a self-service dataset

Create a self-service dataset, select the uploaded Excel dataset, and check the sample fields, as shown in the figure below:

3.2 Intercept specified characters

Click "+" to add "New column", as shown in the figure below:

Name and enter the formula: INDEXOF(SPLIT(number of people with regional education,"\\|"),1), click "OK", as shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Formula description:

Note: Since the "|" in the original field is a keyword in the regular expression, if it is not escaped, "SPLIT" will treat it as a regular expression, so it must be changed to SPLIT(string, "\\|" ).

3.3 Effect display

See section 1.1 of this article for details.

For more details on the content of the intercepted fields, see: Field Classification.