rearranging copied addresses into columns

jknichlsn

New Member
Joined
Mar 4, 2002
Messages
4
First posting; glad I found this.

If I copy a long list of addresses (say, from the Web) arranged in address blocks, like this:

Jack Nicholson
Big Bank Corp.
123 Fourth Street
Second Floor
Chicago, IL 60603

and want to end up with ALL these addresses in columns (all names in Col A, Company in Col B., etc.), how can I do this? To complicate things, some addresses have more rows in the address block than others; I can work that out by sorting the resulting columns and moving the data, unless someone has something very clever in mind.

Alternatively, does anyone know of a commercial product, faily cheap, that will convert addresses into ACT! contact management software format?

thanks!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I had to do the very same thing myself with an output from ACT about a week ago.

The following worked.
Before I started all the addresses were in column A and started in cell A1.
There was also a blank line in between each address and, as in your case, the addresses took up variable numbers of lines.


--
Dim myRow As Integer
Dim myIncrement As Integer


myRow = 2
myIncrement = 1
Range("A1").Select

While ActiveCell.Value <> ""

ActiveCell.Offset(1, 0).Cut
ActiveCell.Offset(0, myIncrement).Select
ActiveSheet.Paste
Rows(myRow & ":" & myRow).Select
Selection.Delete Shift:=xlUp
myIncrement = myIncrement + 1
ActiveCell.Offset(-1, 0).Select
If ActiveCell.Offset(1, 0).Value = "" And ActiveCell.Offset(2, 0).Value <> "" Then
Rows(myRow & ":" & myRow).Select
Selection.Delete Shift:=xlUp
myIncrement = 1
myRow = myRow + 1

End If

Wend
--
As with most of my code, it's probably a bit cumbersome, but seems to do the trick.

Rgds
AJ
 
Upvote 0
wow.

I am quite new to macros, so I don't have any idea how to proceed, but I'm sure I can find someone around here who can take your code and make it do its thing on my lists.

Thank you, VERY much, for taking the time. I really appreciate it.
 
Upvote 0
Well, I thought it would be easy to find out how to convert your text into something that would actually do something, but no.

Would you be so kind as to reply with something very fundamental, like:

Start with the spreadsheet of your listed addresses opened. Click on "Tools/Macros..." and click on "New Macro" ....'

Your response was like that of a native speaker of a foreign language who replied with a flood to the only phrase I've learned in his language. Thanks very much!!
 
Upvote 0
Hello again!

Sorry I've only just noticed your final post to this thread. I don't usually go that far back. (Was just having a final browse before I go for the day).

Anyway, the simple guide to get this to work is as follows:

1. Make a copy of your file incase this totally screws it up!

2. Because I've just given you code above, you need to go directly to the Visual Basic Editor (under Tools>Macros).

3. When you get there, in the project explorer window (usually the box filling the top half of the left side of the screen), right click on your filename and Insert Module

4. At the top of the module type
Sub YourMacroName ()
You'll see this adds End Sub a couple of lines down. Then paste the macro above in between the Sub line and the End Sub line.

5. Back in Excel see if it works...
Tools>Macro>Macros pick YourMacroName and click run.

If it works we're laughing! If it brings up an error message, reply telling me what it is. If it runs all the way through and does not work as you intended, go back to the Visual Basic Editor. Shrink the window a bit so you can see what's going on in Excel at the same time. Put your cursor anywhere in the macro and hit F8 repeatedly to step through it line by line. This will give you a clue as to where it's falling down. Again, reply and me or someone else will fix it!

Rgds
AJ
 
Upvote 0
As to ACT.
ACT will import a text delimited file.
The key to success with the import is to ensure that your column headers are identical to the field names in ACT.
You should split your name column into first name and last name columns. Act will merge these two columns into the contact field automatically.
 
Upvote 0
AJ: you are the best; thanks very much for taking the time to educate a frustrated banker.

Let me know if I can do anything for youl.
 
Upvote 0
Thanks guys.
ASAP, I downloaded it, it's not working for this. Seems like it pretty much just does the normal transpose function, correct?

Tom, I tried several times your idea. I don't know why, but it doesn't work. I failed to mention that I only listed 4 records as an example, I really have 500 records to do this to.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top