Separating numbers in an excel cell


Hi, do you know of any excel formula to count the number of times each number occurs in a particular row? I mean I will have a separate column for each count of the numbers on the right.  




2 Replies



Is this what you are looking for?

Hi @fathy965 

Another interpretation of your need is like


where row 2, BAJOGA's number 1 has been seen three times as well as also number 2.

If so;

     range;  $B2:$E2;
     number; H$1;
numbers;     TEXTJOIN(" ";1;range);
html;        "<tr><td>" & SUBSTITUTE(numbers;" ";"</td><td>") & "</td></tr>";
array;       TRANSPOSE(FILTERXML(html;"//td"));
output;      SUM(--(array=number));

You can change the last output (row to eg html and see how each step transforms data in the final output.