I need to calculate how many time any given numbers appeared in an excel cell.
Conditions below:
Each cell can have different combination of repetitive numbers, separated by commas.
The excel formula will be robust enough to search for any numbers desired by user.
Max range up to 20.
Example, I would input any number and the formula should return me a sum of occurrence.
1, 2, 3, 1, 5, 11, 1, 11
=> 5 (input "1" and "11")
=> 3 (input "1" only)
=> 2 (input "11" only)
*****************************************************
Things i have tried:
=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"1,",""))=> Answer is 7 (wrong)=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"1",""))=> Answer is 8 (wrong)
Main difficulty: The formula seems to search for "1" and therefore "11" would often mistooken as "1" too, and thus added to the sum.
Same goes for "5", "15" etc, any numbers in the tenths.
My approach was to do a text search instead of numbers, that failed.
Appreciate if there are alternate solutions too (Text -> Column ??).