Count String Occurrence in a Cell

Options
Nazeer Sayyed
Nazeer Sayyed ✭✭
edited 03/25/21 in Formulas and Functions

Hi There,

I have below two columns in my sheet. Column [Status (Delivered)] has concatenated values (WIP and Delivered). In [Delivered Count] column, I need to show how many times 'Delivered' string occurred.

Please see below example.


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Nazeer Sayyed

    See if this will work for you

    =(LEN([Status (Delivered)]@row) - LEN(SUBSTITUTE([Status (Delivered)]@row, "Delivered", ""))) / LEN("Delivered")

    This is looking at the length (number of characters) of the entire string, subtracts the non "Delivered" characters, which means what is left should be a string length in multiples of the word "Delivered". By dividing by the Delivered length we can see how many multiples were in the original string.

    cheers

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Nazeer Sayyed

    See if this will work for you

    =(LEN([Status (Delivered)]@row) - LEN(SUBSTITUTE([Status (Delivered)]@row, "Delivered", ""))) / LEN("Delivered")

    This is looking at the length (number of characters) of the entire string, subtracts the non "Delivered" characters, which means what is left should be a string length in multiples of the word "Delivered". By dividing by the Delivered length we can see how many multiples were in the original string.

    cheers

  • Nazeer Sayyed
    Options

    Hi KDM,

    Thanks much for your quick help. The solution  you provides works the way I wanted.

    Appreciate your help. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!