Splitting an Address equally in 2 columns

[MandyCandy]

Hi Everyone,
First time poster, hope I am following all the rules. My dilemma right now is:

I have an excel with around 3000 office addresses all in a single column, I need to split this in two (i:e, Address becomes, Address 1 and Address 2) For EG:

Address	Address 1	Address 2
#203/205, ground floor, Bellendur, Outer Ring Road, Bangalore - 560 103	#203/205, ground floor, Bellendur,	Outer Ring Road, Bangalore - 560 103

<tbody>
</tbody>

As you can see, one issue I have is that some of the 3000 are typed out manually and with Line-Break. I have used a "=clean(substitute" formula to deal with this problem.

My Second issue is that some of the addresses are with Line-Break and without Commas, hence when I used the Clean Formula the words have gotten attached, so using a (Text-to-column- Delimited) is not working completely either.

I am using an excel 2007, and have access to 2010, My deadline for submission is in 24 hours. Praying someone here can help me out.

 

[Myconservator]

First you will have to fix your columns if there was any data copying error at the end just to make it look neat.
This formula will replace any spaces with ", " except the last one (i.e. end of address)
Keep in mind my data is starting in c2 and formula is in a2.

=IF($D2="",(IF(RIGHT($C2,1)=" ",LEFT($C2,LEN($C2)-1),$C2)),(IF(RIGHT($C2,1)=" ",LEFT($C2,LEN($C2)-1),$C2))&", ")

Now that you have fixed your data by copying and pasting your columns one by one you can go to the next step.
This will be the formula for a1 when data is in c1 to j1.

=IF(COUNTA($C1:$J1)=0,"",IF(OR(COUNTA($C1:$J1)=1,COUNTA($C1:$J1)=2),$C1,IF(OR(COUNTA($C1:$J1)=3,COUNTA($C1:$J1)=4),$C1&$D1,IF(OR(COUNTA($C1:$J1)=5,COUNTA($C1:$J1)=6),$C1&$D1&$E1,$C1&$D1&$E1&$F1))))