title: Separate words with commas in Excel 2010
body: <p>I'm trying to use a formula in Excel to separate a bunch of words in a cell with a comma. If there are more than 5 words in the cell, I just want to get the first 5 words. To get the first five words in a cell and separate them by a comma I use this:</p>
  
  <pre><code>=SUBSTITUTE(LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",5))-1), " ", ", ")
  </code></pre>
  
  <p>This works fine. But the problem with this, because of the number 5 here, if I a cell contains less than 5 words, I get an error. I tried to substitute the 5 with this:</p>
  
  <pre><code>LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
  </code></pre>
  
  <p>So my function becomes this:</p>
  
  <pre><code>=SUBSTITUTE(LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1))-1), " ", ", ")
  </code></pre>
  
  <p>But this doesn't work, it gives me an error. Any idea how I can do this please?</p>
  
  <p>Also I would like to ignore the first word if its first character is "-" (without the quotes) and just start from the second word. So in other words, I want something like this:</p>
  
  <p>I love my life very much <strong>should return</strong> I, love, my, life, very<br>
  - I love my life very much <strong>should return</strong> I, love, my, life, very (the "-" is ignored")<br>
  I love my <strong>should return</strong> I, love, my</p>
  
  <p>Thanks in advance for any help</p>