Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Separating data in a single column into separate columns

I have full addresses listed in one column and I have to have the address separated in seperate columns by street name 1, sreet name 2, city, state, zip, etc. Is there a way to do this that isn't one by one typing them out?


  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    Look at the SmartSheet example formulas in the example sheet.


    You will have to use some combo of Len, Find, Right, Left and Mid to accomplish your goal, but it is possible.

    If you FullAddress column is formatted like this

         Name, street address, city, state, zip


    Then use Find to get the postition (Pos) of the line separator; a comma or other character.

    In a new column use Pos to get the left most portion of the entry and you have your Name separated.


    Pos1 = Find(   "," FullAddress)

    NameColumn = LEFT(FullAddress, Pos1 )

    RemainderColumn1 = Right(FullAddress,  Len(FullAddress) - Pos1)


    You would repeat these columns, replacing FullAddress with RemainderColumnX until you got to the end of the address entry. When your ready to share this with users you can hide the extra columns.


    You will get about 80% of the way there.  There are always exceptioins with extra commas in some names, or thinkgs like "main street, Apt #3" that you have to figure out how to deal with as you go throght it.


    Take a couple or three hours to get 95% of the way there depending on your data.


    Good luck,



  • I would probably export to Excel, use the text to columns feature and then paste the column data back in to new columns in the sheet.

This discussion has been closed.