title: Excel: Returning characters to the left and right of the last (or second to last) occurrence?
body: <p>I have a string like this: 100 Stupid Street ; Government Tower 7; Chicago Palm, MA 92111</p>
  
  <p>I want to return all the characters to the left of the last semicolon.</p>
  
  <p>To the left of the last semicolon would be: 100 Stupid Street ; Government Tower 7</p>
  
  <hr>
  
  <p>That was step 1. I would also like to extract characters from this string: Chicago Palm, MA 92111</p>
  
  <p>I want to return all the characters to the right and left of the second to last space.</p>
  
  <p>So,
  To the right of the second to last space would be: MA 92111</p>
  
  <p>To the left of the second to last space would be: Chicago Palm,</p>
  
  <p>If there is a way to remove that last comma in that last formula, that would be even better.</p>
  
  <hr>
  
  <p>This formula has helped me a bit:</p>
  
  <pre><code>=TRIM(RIGHT(SUBSTITUTE(A1,";",REPT(" ",LEN(A1))),LEN(A1)))
  </code></pre>
  
  <p>Cell A1 has the original string. This formula successfully returns everything to the right of the last semicolon.</p>