title: Split text cell by spaces (or any character) for arbitrary number of words, with repeating words
body: <p>I had an Excel file with some text in a column that I needed to swap out some words with a new phrase, but maintain some parts of the original text.  To keep it simple for my user, I wanted to allow filtering by the original words, which meant I needed a column per word.  The Excel "Text to Columns" function might have been able to help, but I didn't bother with it because I heard you needed to specify the number columns.  </p>
  
  <p>Long story short, here's what I came up with:</p>
  
  <hr>
  
  <p>Source Cell = A2</p>
  
  <p>Column with first word: B2</p>
  
  <p>Column with second word: C2</p>
  
  <hr>
  
  <p>A2: This is some example text</p>
  
  <p>B2: =IFERROR(LEFT(A2, FIND(" ", A2)), "")</p>
  
  <p>C2: =IFERROR(LEFT(SUBSTITUTE(MID($A2, FIND(B2, $A2), LEN($A2) - FIND(B2, $A2)), B2, ""), FIND(" ", SUBSTITUTE(MID($A2, FIND(B2, $A2), LEN($A2) - FIND(B2, $A2)), B2, ""))), "")</p>
  
  <p>D2: <em>filled right from C2</em></p>
  
  <p>...</p>
  
  <hr>
  
  <p>What this mess of stuff in C2 is doing is basically chopping up the source cell (A2) based on the word from the previous cell.  So, B2 is looking for the first space it sees, and throwing the rest of the string away, leaving just the word "This". C2 is looking at B2, and removes the word it sees from the value of A2 and looks for the next space.  D2 would look at C2, and so forth.  Unfortunately, this breaks down when there is a repeated word.  </p>
  
  <p>If we change the example text in A2 to "This is some example text, is it a question" ("is" is repeated), it gets stuck repeating the words "is", "some", "example", "text," over and over when filled right.  </p>
  
  <p>I don't really need an answer for my current task, but I was hoping for a complete solution to this situation, as have not found a good solution anywhere.  </p>
  
  <p>Hope this is helpful for someone!</p>