title: How to extract Middle Names using formula
body: <p>I am trying to:</p>
  
  <ol>
  <li>extract clients' middle name(s) from a field; or </li>
  <li>delete the first and last name, leaving the middle name(s)</li>
  </ol>
  
  <p>but I am stuck. I can remove the last word using </p>
  
  <pre><code>=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)
  </code></pre>
  
  <p>I can remove the first word by</p>
  
  <pre><code>=RIGHT(A1,LEN(A1)-FIND(" ",A1))
  </code></pre>
  
  <p>But I can't combine them. I have looked online and found this:</p>
  
  <pre><code>=TRIM(REPLACE(SUBSTITUTE((TRIM(REPLACE(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),0*LEN(A1)+1,LEN(A1),"")))," ",REPT(" ",LEN(A1))),(1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)*LEN(A1)+1,LEN(A1),""))
  </code></pre>
  
  <p>But it doesn't work.</p>