Rows To Columns & Hyperlink to Email Address

Mar 23, 2012
Reaction score
Hello all!

I have a quite large worksheet here with something like this:

Todd Aagard
Office Phone: 425 481-8888
Agent Phone: 425-273-1311
RE/MAX Northwest Realtors
1909 214th St Ste 205
Bothell, WA 98021
View my profile & listings
Send Email

Tim Abbey
Office Phone: 360 459-0428
Agent Phone: 360-459-0428
Abbey Realty, Inc.
4621 Lacey Blvd
Lacey, WA 98503
View my profile & listings
Send Email

Thomas Biehl
Office Phone: 509 736-3344
RE/MAX First Advantage
636 Colorado St.
Kennewick, WA 99336
View my profile & listings
Send Email

I need to transpose about 1,800 addresses listed in column A to 7 or 8 separate columns for name, office, cell, street, and city/state/zip, link, and email. Email address are in Hyperlink, which I also need to extract out,,,

The macro needs to copy the 1st 7 or 8 lines of column 1 and then transpose to row 1 of 7 or 8 separate columns for a mail merge setup. Next, the macro needs to increment down to row 8 or 9 of column 1 and transpose the next address to row 2 of the 7 or 8 mail merge columns.

So there are a few problems here:
1. Extract email address from "Send Email"

I try to use this code:

Function GetAddress(HyperlinkCell As Range)

    GetAddress = Replace _

    (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")

End Function

But it shows me an error message say: Compile error: Argument not optional".​

2. Not all groups of incoming data include all categories

I found some code here but they are all slight different..​
such as


Thanks you guys!



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question