Count String Occurrence in a Cell

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
-
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
-
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
-
Hi KDM,
Thanks much for your quick help. The solution Β you provides works the way I wanted.
Appreciate your help. π
-
@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!
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!