Counting the Number of Identical Characters in a Field

• You can click Save Dashboard As to view the internal steps or operate the steps by yourself.

Expected Effect

The number of identical characters in the field is returned. For example, you can count the number of / in the Account field, as shown in the following figure. 

Implementation Method

1. Create an analysis subject and upload data.

2. Click Formula Column, and nest the SUBSTITUTE function or the REPLACE function inside the LEN function to achieve the effect. This document uses the nesting of the SUBSTITUTE function and the LEN function as an example.

3. Save and update the data to view the effect.

Sample data: Counting the Number of Identical Characters.zip

Data Upload

1. Click New Subject in My Analysis to create an analysis subject and upload the downloaded sample data, as shown in the following figure.

2. After uploading User Permission Table, select Sheet2, and modify the field type as needed. In this example, modify the field type of Password to Text. Click OK to complete the upload. 

Field Selection

Remain the default selection of all fields and rename the new analysis subject Counting the Number of Identical Characters, as shown in the following figure.

Column Adding

1. Click Formula Column, as shown in the following figure.

2. Since the added column is used to count the number of the / (slash) in the Amount field, enter LEN(Account)-LEN(SUBSTITUTE(Account,"/","")). Account must be selected from the left text fields. Otherwise, the function will be illegal. Finally, click OK, as shown in the following figure. 

The following table explains the formula in detail.

3. After adding the column Counting the Number of Slashes, click Save and Update to save and update the created analysis subject, as shown in the following figure.

For details, see section "Expected Effect."