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. 🙂

Philip Robbins

@Kelly Moore this is nearly 4 years old, but I thought I'd let you know that it was exactly the solution to a problem I was trying to solve yesterday. LEN definitely is one of the most underrated functions!

Kelly Moore

Hey @Philip Robbins

Yes! Definitely an underrated function. Because the function returns a numeric value it can be inserted in many scenarios. I'm glad the formula worked for you.

Kelly