A common question that arises is how to extract part of a string. As an example, here's a table of addresses that might appear in an app. To demonstrate, I'll take the first address from this table and describe how to extract various sections of this address.

The key to extracting parts of a string rely on the Split, and what I call the Last/FirstN technique. I described this in a previous post.

In our test data, the house number appears as the first set of characters, followed by a space. So to extract the house number, we call the Split function to split the full address into a table by the space character, and we then return the first record with the First function. Taking one of the addresses as an example, this is the formula we would use:

First(Split("7 Garden St, Downers Grove, IL, 60515", " ")).Result

This function returns 7.

To return the second line of the address ("Downers Grove"), we would use this formula:

Trim(
     Last(FirstN(Split("7 Garden St, Downers Grove, IL, 60515", ","),2)).Result
)

This formula splits the full address by the "," character. We use the Last/FirstN technique to return the 2nd row from the result of the split. The Trim function removes the preceding space from " Downers Grove". 

In the next example, the State always appears as the second last block of text in the address when separated by the comma character. So to return the state ("IL"), this is the formula we would use.

Trim(
     First(LastN(Split("7 Garden St, Downers Grove, IL, 60515", " "), 2)).Result
)

In this example, we 'invert' the Last/FirstN technqiue and use First/LastN instead return the second from last row from the table that we've split.

Finally, to return the zip code, we can just split the string by a "," and return the last row. This is how the formula would look:

Last(Split("7 Garden St, Downers Grove, IL, 60515", " ")).Result