title: How do you parse a street address from a single cell in Excel into individual cells when the address format isn't consistent?
body: <p>I have a list of addresses which are individual strings in an <code>Excel spreadsheet</code>:</p>
  
  <pre><code>123 Sesame St New York, NY 00000
  123 Sesame Ct Atlanta, GA 11111
  100 Sesame Way, 400 Jacksonville, FL 22222
  </code></pre>
  
  <p>As you can see above the third address is different. It has a suite number of <code>400</code> on what would normally be the <code>street line 2</code> line. I am having trouble coming up with a formula that will parse the addresses above into its individual cells: <code>Street 1</code> (with <code>street 2</code> or suite information in this line), <code>City</code>, <code>State</code> and <code>Zip</code>.</p>
  
  <p>My thought is to start from the right and extract information based on a space delimiter, but I am not sure how to do this. How would I go about this?</p>