Seperating single cell addresses into multiple columns

**Tom_J_W** · 06-11-2013, 01:18 AM

Hello,

I've been given a list of 10'000 addresses, each in a single cell in its own row. I need to separate them into 4 rows: Unit Number, Street Number, Street name and Street type. This would be simple if address contained the same amount of spaces to separate them with however some addresses is a unit it contains an extra space to separate the unit number from the street number.

If I could use text to columns to separate pushing data to the left instead of the right this would be pretty easy but I don't believe its possible. I'm fairly certain I'll have to do a lot of the grunt work myself but any ideas would be great. I've tried setting the last column to filter out all but blanks and then pasting one row to the right but the data doesn't paste straight right due to hidden columns etc

Excel 2010 btw Example_photo.png

**MarvinP** · 06-11-2013, 01:22 AM

Hey Tom,

First try the TRIM() function that will reduce all double spaces to single spaces. Then put up a sample of what you have. It may be a lot of grunt work, but let us look to make sure there aren't easier ways....

To post a sample file, click on "Go Advanced" and then the Paper Clip Icon above the message area.

**Tom_J_W** · 06-11-2013, 01:29 AM

Example.xlsx

Here you go, I just had to mess up the addresses a bit for security reasons

**Ghozi Alkatiri** · 06-11-2013, 02:28 AM

write at B2
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"/"," ")," ",REPT(" ",LEN($A1))),(COLUMN(A1)-1)*LEN($A1)+1,LEN($A1)))

copy to right and down

**Ghozi Alkatiri** · 06-11-2013, 02:30 AM

sorry the first formula write at B1

**Tom_J_W** · 06-11-2013, 08:35 PM

Hi Ghozi,

Thanks for your assistance but all it seems to be doing is splitting the cells from left to right, this means any cells with an extra seperation (say a U at the front) are pushed a column further to the right.

**Ghozi Alkatiri** · 06-11-2013, 09:15 PM

change formula at B1 before with
=MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE($A1,"/"," "),"U",""))," ",REPT(" ",LEN($A1))),((COLUMN(A1)-1)*LEN($A1))+1,LEN($A1))

drag right and down

**Tom_J_W** · 06-11-2013, 09:59 PM

Ah I see now, not sure how I misunderstood that to be honest.

When I say bound to the right I don't mean to the right side of the cell, I mean when I use text to columns it pushes the new columns right, What O meed is to push the new columns left instead