How to count comma separated specific values in a single cell in excel. Suppose to found how many 15's are there in a single cell that contains 15,115,1515,15,215,15 I need the result 3. Is there any formula in excel
Asked
Active
Viewed 318 times
0
-
1See this [link](https://stackoverflow.com/q/61837696/9758194) – JvdV Aug 01 '20 at 14:09
-
@JvdV I thought of that, but then considered that `FILTERXML` probably wouldn't differentiate `015` and `15` and `15.00` – Ron Rosenfeld Aug 01 '20 at 16:20
-
@RonRosenfeld and your idea is that it should or should not count towards n? `//s[.='15']` does not take `015` or `15.00` as the same inputs. – JvdV Aug 01 '20 at 17:53
-
1@JvdV Ah. My error. I was using `.=15` and not `.= '15'` – Ron Rosenfeld Aug 01 '20 at 21:05
1 Answers
2
One way is to compare the length of the original value with the length of the value after you've replaced all the 15s with a zero-length string, and divide that by 4 (which is the length of ",15,").
One important note... make sure to add a comma to the start and end of the original string to pick up leading and trailing 15s.
Here's what it would look like...
John Joseph
- 1,003
- 1
- 10
- 20