Embedded e-mail addresses 1

[merc007]

Hello,

I have an excel spreadsheet that has embedded e-mails in the cells.

Ex. Cell A1 is titled "Email" and then A2 and so on either say "Yes" or the cell is empty. If the cell says yes there is a hyperlink to the e-mail address so when clicked on it automatically opens your local e-mail editor. I would like to extract the embedded e-mail addresses to their own cell if possible. Is there a way to do this?

I appreciate the help.

 

[firefytr]

Sure, but what cell has the hyperlink and how is the hyperlink created?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[merc007]

The cells are in column "F". They start with "F2" and continue to an uncertain row number... (the number of records will change daily) The links are always there. Not quite sure how they got there, however, i can right click and choose edit hyperlink and see them in the hyperlink properties.

 

[firefytr]

And the hyperlink should say:

mailto:address@domain.com

Yes?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[merc007]

yes that is correct

 

[firefytr]

You'll need a UDF (User Defined Function) for that...

Public Function GetHyperlink(ByVal Cell As Range) As String
    Dim strValue As String, iLen As Long
    GetHyperlink = "ERROR!"
    If Cell.Hyperlinks.Count = 1 Then
        strValue = Cell.Hyperlinks(1).Address
    Else
        If Left(Cell.Formula, 11) = "=HYPERLINK(" Then
            If InStr(1, Cell.Formula, ",") <> 0 Then
                iLen = InStr(1, Cell.Formula, ",") - 21
                strValue = Mid(Cell.Formula, 20, iLen)
            End If
        End If
    End If
    If Len(strValue) = 0 Then Exit Function
    If Left(strValue, 7) = "mailto:" Then
        GetHyperlink = Right(strValue, Len(strValue) - 7)
        Exit Function
    End If
    GetHyperlink = strValue
End Function

Let us know if you need help installing it. This will do the HYPERLINK formula as well.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[merc007]

Thank you... and yes, i'll take you up on your offer of helping me install this code... whats the best way to go about that?

 

[firefytr]

From Excel, hit Alt + F11 to open the Visual Basic Editor (VBE)
Hit Ctrl + R to open the Project Explorer (PE)

.. IF you want this available to all workbooks, select your Personal.xls in your PE
.. IF you want this available only in the workbook in question, select it in the PE

Click Insert | Module
Paste the code on the right pane.
Click the save icon to save your work
Close the VBE

Now, call like a regular function (i.e. SUM, AVERAGE, etc) in Excel ...

=GETHYPERLINK(A1)

Note that the first time you type this in will make the case sensitivity for the UDF. So if you type it in like so ...

=getHyperlink(A1)

.. then that is how it will show up in all subsequent formulas like it. So type it how you want it the first time.


HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[merc007]

That is AWESOME!!! Thank you very much.. It works perfect

 

[firefytr]

Glad you like it.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci