Rows To Columns & Hyperlink to Email Address

R

rosefei

Joined
Mar 23, 2012
Messages
1
Reaction score
0
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:

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

Code: 
=INDEX($A:$A,(ROWS($1:1)-1)*4+COLUMNS($A:B)-1)

Thanks you guys!

Rose
 
Back
Top