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

2 Replies

  • 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