Is it possible to separate an address into Street, City, and State?

I have a column of addresses that I want to separate into 3 columns for street, city, and state.

For example: 1 Tesla Road, Austin, Texas, 78725

Street: 1 Tesla Road

City: Austin

State: Texas

Is there a formula I could use to separate the address into 3 columns by the commas?

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Here are some slight variations using SUBSTITUTE():

    Street:

    =LEFT([Address]@row, FIND(",", [Address]@row) - 1)

    City:

    =MID([Address]@row, FIND(",", [Address]@row) + 1, FIND("!", SUBSTITUTE([Address]@row, ",", "!", 2)) - FIND(",", [Address]@row) - 1)

    State:

    =MID([Address]@row, FIND("!", SUBSTITUTE([Address]@row, ",", "!", 2)) + 1, FIND("!", SUBSTITUTE([Address]@row, ",", "!", 3)) - FIND("!", SUBSTITUTE([Address]@row, ",", "!", 2)) - 1)

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers

    @Carlee Schiffner If the data is consistent, you could use a mid combined with find formula

    This would be the street

    =mid(address@row, 1, find(",",address@row,1)-1)

    That will get the first piece. The second and third get a little trickier because your start position can't be 1 and you have to find the length of the second section.

    =MID(Address@row, FIND(",", Address@row, 1) + 1, FIND(",", Address@row, FIND(",", Address@row, 1) + 1) - FIND(",", Address@row, 1) - 1)

    and then the same concept with the third

    =MID(Address@row, FIND(",", Address@row, FIND(",", Address@row, +1) + 1) + 2, FIND(",", Address@row, FIND(",", Address@row, FIND(",", Address@row, 1) + 1) + 1) - FIND(",", Address@row, FIND(",", Address@row, +1) + 1) - 2)

    You'll have to change 'address@row" with whatever columnname you keep your address

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Here are some slight variations using SUBSTITUTE():

    Street:

    =LEFT([Address]@row, FIND(",", [Address]@row) - 1)

    City:

    =MID([Address]@row, FIND(",", [Address]@row) + 1, FIND("!", SUBSTITUTE([Address]@row, ",", "!", 2)) - FIND(",", [Address]@row) - 1)

    State:

    =MID([Address]@row, FIND("!", SUBSTITUTE([Address]@row, ",", "!", 2)) + 1, FIND("!", SUBSTITUTE([Address]@row, ",", "!", 3)) - FIND("!", SUBSTITUTE([Address]@row, ",", "!", 2)) - 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I've been using the SUBSTITUTE method for a while now. I believe I may have first seen it from @L_123.

    I usually start and end the string with the common delimiter too. That way I can use a cell reference to make the piece number more dynamic instead of having to change it for each individual piece and it can be dragfilled when going across columns in the same row (as in this case) or applied as a column formula when parsing down a column.

    =MID([Address]@row, FIND("!", SUBSTITUTE("," + [Address]@row + ",", ",", "!", Number@row)) + 1, FIND("!", SUBSTITUTE("," + [Address]@row + ",", ",", "!", Number@row + 1)) - FIND("!", SUBSTITUTE("," + [Address]@row + ",", ",", "!", Number@row)) - 1)

  • L_123
    L_123 ✭✭✭✭✭✭

    Yeah, substitute is a powerful solution for these. You could also stack left rights, but that gets pretty deep after the second iteration.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L_123 I tried the left/rights when I first started parsing and found it was easier to stack FINDs, but even that gets a bit crazy after a short few. Your SUBSTITUTE solution really saved the day and has become one of my personal "go-to's" as a best practice (and my team has also adopted it as well).

  • Carlee Schiffner
    Carlee Schiffner ✭✭✭
    edited 07/19/23

    Awesome, thank you! This worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!