Counting the Number of Occurrences of a Character in a Field

  • Last update:  2022-05-10
  • 1. Overview

    1.1 Expected view

    Count the number of occurrences of the same character in the field. For example, count the number of occurrences of "/" in the "Account" field. As shown below:


    1.2 Implementation ideas

    1) After uploading the data, add the fields to be analyzed in the self-service dataset.

    2) Then add a formula in the newly added column, and use the nested formula of the SUBSTITUTE function and the LEN function to achieve the effect.

    3) After saving the self-service dataset, update the data to view the effect.

    2. Operation steps

    Sample usage data: count the number of same characters.rar

    2.1 Upload data

    Add "EXCEL Dataset" under "Package" in "Data Preparation", upload the original data, as shown below:


    After uploading the "User Authority Table", modify the table name to "Count the same characters", modify the field type as needed, and click "OK" to finish uploading, as shown below:


    2.2 Create a new self-service dataset

    After uploading the data, continue to add the "self-service dataset" under the "package" to analyze and process the data, as shown below:


    2.3 Selection field

    Add a table name to the newly created self-service dataset, and then select all the fields in the uploaded EXCEL table "Count the same characters" and add them to the dataset, as shown in the following figure:


    2.4 New column

    1) Add "New Column", as shown in the figure below:


    2) Add a new column to count the number of "/" in the "Account" field. Enter the formula:

     LEN(account)-LEN(SUBSTITUTE(account,"/","")), where the "account" field needs to be added in the text field on the left to be effective. Click "OK" when finished, as shown below:

    The formula description is shown in the following table:

    LEN(Account)Count the string length of the "Account" field.

    LEN(args): Returns the number of characters in the text string or the length of the array.

    SUBSTITUTE(Account,"/","")Replace the "/" in the "Account" field with a null value to achieve the effect of removing the "/".SUBSTITUTE(text,old_text,new_text,instance_num): Replace old_text in the text string with new_text.
    LEN(SUBSTITUTE(Account,"/",""))The string length of the "account" field with "/" removed.-
    LEN(Account)-LEN(SUBSTITUTE(Account,"/",""))The length of the "account" field is subtracted from the length of the "account" field after removing the "/" to get the number of "/".-


    After adding the "Count the number of '/' " column, click "Save" to complete the data processing, as shown below:


    Click "Update Data" to update the created self-service dataset, as shown in the figure below:


    3. View the effect

    For details of the effect, please refer to section 1.1 of this article.


    主题: Advanced Data Analysis
    • Helpful
    • Not helpful
    • Only read





    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    Cannot be empty

    Submitted successfully

    Network busy