Extract Value From String

[Shaun_Hughston]

I’ve tinkered with a few ways of doing this, but wanted to get some ideas about the most efficient way.

I have this full address string:

Street address: 1600 Pennsylvania Ave NW # 15 City: Washington State: DC ZIP Code: 20500-0003

I want to extract the State (in this case, DC).

I’ve tried LEFT, RIGHT, MID, etc but nothing seems to zero in on it.

Appreciate the help

:slightly_smiling_face:

[Jason · Accepted solution]

Hi @Shaun_Hughston!

You’re on the right track with using MID, but you’ll first need to use FIND to locate some portion of text in the address field. I’d suggest locating the “State:” text with FIND, and then you use combine that with MID to extract only the two digit abbreviation.

MID({Address}, FIND("State:",{Address}) + 7, 2)

The +7 in the formula is used to set the starting point for MID just before ‘DC’, and the 2 used in the formula designates how many characters to count.

As long as the state portion of your address field follows the same structure as the example you gave (e.g. State: XX), this formula will extract the state abbreviations.

[Shaun_Hughston]

Thanks Jason, that’s awesome.

To expand on that, if I want to pull out the city portion (which has a variable number of characters vs the state which is always 2), what would be the method for that? Can I define boundaries around a string (like find the part between City: and State:)?

[Jason]

Shaun_Hughston:

To expand on that, if I want to pull out the city portion (which has a variable number of characters vs the state which is always 2), what would be the method for that?

You can define boundaries through a combination of MID, FIND, and LEFT; here’s how.

Step 1

First, isolate the only the text starting with the city; this will include the rest of the string as well.

MID(
  {Address}, 
  FIND(
     "City:",
     {Address}
  )
  + 6, 50
)

Step 2

Then, use another similar formula to extract *just* the city name from the result of the above formula. In this step, you'll need to include the entire formula used above as part of another formula. I'll include that as a placeholder (INSERT FORMULA FROM STEP 1) for now.

LEFT(
   INSERT FORMULA FROM STEP 1,
      FIND(
         "State:",
         INSERT FORMULA FROM STEP 1
      )
   -1
)

Step 3

Replace the placeholder (INSERT FORMULA FROM STEP 1) with the actual formula from step 1. When combined together, the entire formula would be:

LEFT(
      MID(
        {Address}, 
        FIND(
           "City:",
           {Address}
        )
        + 6, 50
      ),
      FIND(
         "State:",
         MID(
           {Address}, 
           FIND(
              "City:",
              {Address}
           )
           + 6, 50
         )
      )
   -1
)

Here’s a screenshot showing the final result:

Hope that helps!

[Shaun_Hughston]

Jason:

LEFT( MID( {Address}, FIND( “City:”, {Address} ) + 6, 50 ), FIND( “State:”, MID( {Address}, FIND( “City:”, {Address} ) + 6, 50 ) ) -1 )

That’s absolutely awesome, thanks Jason!

[Jason]

Sure thing Shaun, happy to help.