Forum Discussion

fathy965's avatar
fathy965
Copper Contributor
Feb 18, 2022

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.     ...
Formulas and Functions
bosinander's avatar
bosinander
Iron Contributor
Feb 18, 2022

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;

=LET(
     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));
output
)

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

Resources