Search code examples
excelparsingexcel-formulaarcmap

tool like MID but that counts from the right

I have a list of city, state, and zip lines that are combined in Excel. I want to use a tool like MID that takes a specific length of characters from a specified start point, but I want the start point to be counted in from the right.

Is there a "=MID('cellname', -9,2)" or something to pull a specified length of characters from a designated start point from the right?

Ex:

North Adams MA 01095

Springfield MA 12222

East Longmeadow MA 22222

In these instances I want just MA. Since the zip code is always five digits, I know which characters are the state code as long as I count from the right.

Thank you for your help

Solution

  • There is no function to do this directly. But, you can chain together RIGHT and LEFT like so:

    =LEFT(RIGHT(K6,8),2)

    The RIGHT function will return the last 8 characters, i.e. MA 22222. Then, we can use the LEFT function to pull out the first 2 characters, i.e. the state abbreviation.