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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Address[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[/TR]
[TR]
[TD]#203/205, ground floor,
Bellendur,
Outer Ring Road,
Bangalore - 560 103
[/TD]
[TD]#203/205, ground floor,
Bellendur,
[/TD]
[TD]Outer Ring Road,
Bangalore - 560 103
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Ombir]

Please provide link to your excel file with expected output for some rows.

 

[Myconservator]

Follow these steps and hope it works.
Remember to backup your file before doing any of these in case you lose the data.

Press ctrl+F and select the replace option from top in that window.
Click in the find what: and press alt key, while alt key is pressed quickly type 010 and a small dot will appear in that field.
Now in replace with: type ", " or in other words "comma space"

Select replace all and see if it fixed your problem of having line breaks in the column.
If it did not than follow these steps:

Press ctrl+F and select the replace option from top in that window.
Click in the find what: and press alt key, while alt key is pressed quickly type 013 and a small dot will appear in that field.
Now in replace with: type ", " or in other words "comma space"

Now you have replaced all the line breaks with ", "
Next problem is that in some data people might have used comma before enter line break or used comma space before entering line break
Filter those out by using

Find what: ", , " or "comma space comma space"
Replace with: ", " or "comma space"

Hit replace all

Find what: ",, " or "comma comma space"
Replace with: ", " or "comma space"

Hit replace all

Now your data is ready to work with as you wish.

 

[MandyCandy]

Please provide link to your excel file with expected output for some rows.

Is there an option here? Or do u mean a dropbox type link?

 

[MandyCandy]

Follow these steps and hope it works.
Remember to backup your file before doing any of these in case you lose the data.

Press ctrl+F and select the replace option from top in that window.
Click in the find what: and press alt key, while alt key is pressed quickly type 010 and a small dot will appear in that field.
Now in replace with: type ", " or in other words "comma space"

Select replace all and see if it fixed your problem of having line breaks in the column.
If it did not than follow these steps:

Press ctrl+F and select the replace option from top in that window.
Click in the find what: and press alt key, while alt key is pressed quickly type 013 and a small dot will appear in that field.
Now in replace with: type ", " or in other words "comma space"

Now you have replaced all the line breaks with ", "
Next problem is that in some data people might have used comma before enter line break or used comma space before entering line break
Filter those out by using

Find what: ", , " or "comma space comma space"
Replace with: ", " or "comma space"

Hit replace all

Find what: ",, " or "comma comma space"
Replace with: ", " or "comma space"

Hit replace all

Now your data is ready to work with as you wish.

Thanks this really worked wonders, one small issue I faced was, for some of the Line-Break, I guess the typer, put Alt+Enter one or two times before starting to type, hence there were "Comma space"s at the start of the address as well.

However, while using Delimited I guess this will go away.

Do u recommend now using "Delimited, followed by '&'" formula? My problem with that is that with almost 3000 Addresses, there will be no uniformity.
For Eg:
This is one address (G-2, Plot No. 44, Balaji Enclave, Road No. 2, Income Tax Officers Colony, Ramakrishnapuram, Saroornagar Village and Mandal, Kotha Pet, Hyderabad – 500035, Andhra Pradesh.)
And this is another (Haldia City Center, Bhawbani Pore - 721657)

Appreciate the assistance. You are really saving me time from the alternative of doing this manually.

 

[Myconservator]

Here is what I would do:

First make a backup of your file so that if you lose data you can revert back to original file.

Now take the column that has addresses and copy it into a brand new sheet.
Now select this column and go to data and select text to column. Select delimited and put ", " as your delimiter (in others).

This will spread your data in a few to many columns.

Now insert two columns in the beginning and place a formula which checks how many cells are filled next to it and concatenate accordingly.

So if there are only 2 cells filled than first one will take one and second column will take the other
If there are 3 cells filled than first one will take 2 and second one will take 1
for 4: 2,2
for 5: 3,2
For 6: 3,3

 

[Ombir]

Is there an option here? Or do u mean a dropbox type link?

Any link to download the file will work !

 

[MandyCandy]

Here is what I would do:

First make a backup of your file so that if you lose data you can revert back to original file.

Now take the column that has addresses and copy it into a brand new sheet.
Now select this column and go to data and select text to column. Select delimited and put ", " as your delimiter (in others).

This will spread your data in a few to many columns.

Now insert two columns in the beginning and place a formula which checks how many cells are filled next to it and concatenate accordingly.

So if there are only 2 cells filled than first one will take one and second column will take the other
If there are 3 cells filled than first one will take 2 and second one will take 1
for 4: 2,2
for 5: 3,2
For 6: 3,3

"Now insert two columns in the beginning and place a formula which checks how many cells are filled next to it and concatenate accordingly"

Countif?

 

[MandyCandy]

"Now insert two columns in the beginning and place a formula which checks how many cells are filled next to it and concatenate accordingly"

Countif?

Sorry about the stupid question, My mind is just numbing away, I got it, (COUNTA formula..)

Thanks a ton Myconservator and Ombir, finally sorted. Have a good one.. Cheers!!

 

[MandyCandy]

Here is what I would do:

First make a backup of your file so that if you lose data you can revert back to original file.

Now take the column that has addresses and copy it into a brand new sheet.
Now select this column and go to data and select text to column. Select delimited and put ", " as your delimiter (in others).

This will spread your data in a few to many columns.

Now insert two columns in the beginning and place a formula which checks how many cells are filled next to it and concatenate accordingly.

So if there are only 2 cells filled than first one will take one and second column will take the other
If there are 3 cells filled than first one will take 2 and second one will take 1
for 4: 2,2
for 5: 3,2
For 6: 3,3

Myconservator; I thought I had it sorted but it dint work, can u give me the formula for:

(Now insert two columns in the beginning and place a formula which checks how many cells are filled next to it and concatenate accordingly.

So if there are only 2 cells filled than first one will take one and second column will take the other
If there are 3 cells filled than first one will take 2 and second one will take 1
for 4: 2,2
for 5: 3,2
For 6: 3,3)