Count String Occurrence in a Cell

Nazeer Sayyed

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.

Kelly Moore

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

Kelly Moore

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

Hi KDM,

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

Appreciate your help. 🙂