Extracting Content After Repeated Specified Characters

Expected Effect

You may need to extract characters at specified positions following a particular character that appears multiple times within a field value.

For instance, you may need to extract the substrings after the last underscores (_) within variable-length strings, as shown in the following figure.

Alternatively, you may need to extract the values of the field B in the A|B|C field. For example, you may need to extract all educational background information, as shown in the following figure.

Implementation Method

You can extract the character(s) either through the one-click splitting by separator or through a combination of functions such as INDEXOF, FIND, MID, SUBSTITUTE, and RIGHT.

Example data: Extracting Content After Repeated Specified Characters.xlsx

You can upload the example data to FineBI, as shown in the following figure.

Creating an Analysis Subject

For details about how to create an analysis subject, see Creating an Analysis Subject.

Extracting Specified Characters

Click Formula Column, set Name of the Added Formula Column to Characters After the Last Underscore (_), enter the formula INDEXOF(SPLIT(Field,"_"),LEN(SPLIT(Field,"_"))-1), and click OK, as shown in the following figure. INDEXOF(SPLIT(a_b_c,"_"),LEN(SPLIT(a_b_c,"_"))-1)  

The following table describes the formula in detail.

Method Three

1. Click Formula Column, set Name of the Added Formula Column to Counting the Characters After the Array is Split by the Underscores, enter the formulaLEN(SPLIT(Field,"_")), and click OK. For example, LEN(SPLIT(a_b_c,"_")) returns 3, as shown in the following figure.

2. Click Formula Column, set Name of the Added Formula Column to Replacing the Last Underscore with a Hyphen, enter the formula SUBSTITUTE(Field,"_","-",LEN(SPLIT(Field,"_"))-1), and click OK, as shown in the following figure.

3. Click Formula Column, set Name of the Added Formula Column to Position of the Hyphen Starting from the First Character, enter the formula FIND("-",SUBSTITUTE(Field,"_","-",LEN(SPLIT(Field,"_"))-1),1), and click OK, as shown in the following figure.

4. Click Formula Column, set Name of the Added Formula Column to Counting the Characters After the Last Hyphen, enter the formula LEN(Field)-FIND("-",SUBSTITUTE(Field,"_","-",LEN(SPLIT(Field,"_"))-1),1), and click OK, as shown in the following figure.

5. Click Formula Column, set Name of the Added Formula Column to Extracting Specified Characters, enter the formula RIGHT(Field,LEN(Field)-FIND("-",SUBSTITUTE(Field,"_","-",LEN(SPLIT(Field,"_"))-1),1)), and click OK, as shown in the following figure.

Effect Display

For details, see the "Expected Effect" section.

Note:

The length of each value of field B is inconsistent.

Example data: Recruitment Information.xlsx

Upload the example data to FineBI, as shown in the following figure.

Creating an Analysis Subject

For details about how to create an analysis subject, see Creating an Analysis Subject.

Extracting Specified Characters

Method One

Choose More > Split Field on the data editing page to enter the function page, as shown in the following figure.

Split the field by the separator (|) and enter 2 in Split Result, as shown in the following figure.

Method Two

Click Formula Column, set Name of the Added Formula Column to Extracting the Middle Characters, enter the formula INDEXOF(SPLIT(RegionEducationCount,"|"),1), and click OK, as shown in the following figure.

The following table describes the formula in detail.

Effect Display

For details, see the "Expected Effect" section.

For details about extracting specific field content, see Data Merging and Splitting.