MrExcel Publishing
Your One Stop for Excel Tips & Solutions

reformat address info

Posted by James King on July 16, 2001 9:00 PM

I have several excel files that contain address information in the following format:

Cell A1:Name
Cell B1:Address
Cell C1:City
Cell D1:blank

I want to reformat the information so Column A is Name, Column B is Address and Column C is City. Currently, I am cutting and pasting from the rows to the columns but it takes too much time. Is there a way to automate this reformatting? I tried to record a macro, my first time, but the macro was specific to the cells I was reformatting. If I could position the cursor in a cell and run a macro from there, that would be a step in the right direction. I hope my request is clear. Thanks for any help.


Posted by Aladin Akyurek on July 17, 2001 2:03 AM


You might want to try the following. if you don't have any duplicates

Lets take the sample that follows:

{"n1";"a1";"c1";0;"n2";"a2";"c2";0;"n3";"a3";"c2";0;"n4";"a4";"c4";0;"n5";"a5";"c5"} [ where 0 stands for blank ]

The above data occupy A1:A19.

In B1 enter: =ADDRESS(ROW(),1)
In C1 enter: =ADDRESS(ROW()+1,1)
In D1 enter: =ADDRESS(ROW()+2,1)

In B2 enter: =ADDRESS(ROW(INDIRECT(B1))+4,1)

Copy the formula in B2 first to C2:D2 then down until the cell address of the last cell in column A appears in column D.

In E1 enter: =INDIRECT(B1)

Copy the formula in E1 first to F1:G1 then down up to last row of data in column D.

Now select the range of data in F to G, do a copy and Paste Special|Values. Then delete columns A to D.



Posted by Aladin Akyurek on July 17, 2001 2:07 AM

Typo ...