Separating data in a single column into separate columns

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