Separating numbers in an excel cell

%3CLINGO-SUB%20id%3D%22lingo-sub-3183672%22%20slang%3D%22en-US%22%3ESeparating%20numbers%20in%20an%20excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3183672%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20do%20you%20know%20of%20any%20excel%20formula%20to%20count%20the%20number%20of%20times%20each%20number%20occurs%20in%20a%20particular%20row%3F%20I%20mean%20I%20will%20have%20a%20separate%20column%20for%20each%20count%20of%20the%20numbers%20on%20the%20right.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22fathy965_0-1645184640726.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349460iA2A7425AE57D3972%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22fathy965_0-1645184640726.png%22%20alt%3D%22fathy965_0-1645184640726.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3183672%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3184307%22%20slang%3D%22en-US%22%3ERe%3A%20Separating%20numbers%20in%20an%20excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3184307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572595%22%20target%3D%22_blank%22%3E%40fathy965%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIF(%24B%241%3A%24AG%241%2CA2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3184758%22%20slang%3D%22en-US%22%3ERe%3A%20Separating%20numbers%20in%20an%20excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3184758%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572595%22%20target%3D%22_blank%22%3E%40fathy965%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20interpretation%20of%20your%20need%20is%20like%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_0-1645197336098.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349500iD29EF9957C2CD79A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22bosinander_0-1645197336098.png%22%20alt%3D%22bosinander_0-1645197336098.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ewhere%20row%202%2C%20BAJOGA's%20number%201%20has%20been%20seen%20three%20times%20as%20well%20as%20also%20number%202.%3C%2FP%3E%3CP%3EIf%20so%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20%20%20%20range%3B%20%20%24B2%3A%24E2%3B%0A%20%20%20%20%20number%3B%20H%241%3B%0Anumbers%3B%20%20%20%20%20TEXTJOIN(%22%20%22%3B1%3Brange)%3B%0Ahtml%3B%20%20%20%20%20%20%20%20%22%22%20%26amp%3B%20SUBSTITUTE(numbers%3B%22%20%22%3B%22%22)%20%26amp%3B%20%22%22%3B%0Aarray%3B%20%20%20%20%20%20%20TRANSPOSE(FILTERXML(html%3B%22%2F%2Ftd%22))%3B%0Aoutput%3B%20%20%20%20%20%20SUM(--(array%3Dnumber))%3B%0Aoutput%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20change%20the%20last%20output%20(row%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%20to%20eg%20html%20and%20see%20how%20each%20step%20transforms%20data%20in%20the%20final%20output.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

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.  

 

fathy965_0-1645184640726.png

 

2 Replies

@fathy965 

=COUNTIF($B$1:$AG$1,A2)

Is this what you are looking for?

Hi @fathy965 

Another interpretation of your need is like

bosinander_0-1645197336098.png

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.