title: match the number from comma separated value and count
body: <p>I have a comma separated value in A2 and same numbers in different cells B1, C1, D1.... I want to match them from comma separated value and find out the count in B2, C2, D2. Please see the image attached you will get the context.</p>
  
  <p><a href="https://i.stack.imgur.com/P7tsd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/P7tsd.png" alt="enter image description here"></a></p>
  
  <p>can anyone guide me if we can achieve this by formula or macro in excel.</p>
  
  <p>Tried formula:</p>
  
  <pre><code>=LEN(TRIM($A$2))-LEN(SUBSTITUTE(TRIM($A$2),C1,","""))
  </code></pre>
  
  <p>any help will be highly appreciated.</p>
  
  <p>Also, I have two data sets where I will be using this formula to find out the count of number from comma-separated value and based on count I want the repeated ones to come in a different cell please refer the image for better understanding.</p>
  
  <p><a href="https://i.stack.imgur.com/ZR2Mz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZR2Mz.png" alt="enter image description here"></a></p>