Separating data in a single column into separate columns

Kathryn Aquino

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

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.

EXAMPLE COLUMNS

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,

Brett

 

Alina Anderson

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.