Microsoft 2007 Excel

Larry_from_LA · 03-07-2009, 06:30 PM

I have an excel spreadsheet and I would like every entry in a specific column to be an e-mail address.

Over 1,000 of the entries in this e-mail column are not formated for an e-mail address with the "mailto" preceeding the text.

I currently have to right click on the cell, then when the drop down menu appears I click on "Hyperlink", then when the dialog box opens I have to copy what appears in the "Text to display:" area and paste it into the "E-mail address:" area.

By doing this for every cell I get mailto:xxx@abc.com and a valid e-mail address.

I have over 1,000 cells to which I am required to do the above process one at a time.

Can the cells be converted to e-mail addresses in a more efficient manner?

**macropod** · 03-10-2009, 05:34 AM

Hi Larry,

Assuming you want to ensure both the 'mailto:' and the hyperlinking are applied to all cells with email addresses in a selected range, you could use a macro along the lines of:

Code:

Sub E_MailFormatter()
'macro created 10/03/2009 by macropod
Dim oCel As Range
For Each oCel In Selection
  If InStr(oCel.Value, "@") > 0 Then
    If Left(oCel.Value, 7) <> "mailto:" Then oCel.Value = "mailto:" & oCel.Value
    oCel.Hyperlinks.Add oCel, oCel.Value
  End If
Next
End Sub

BTW, some feedback on the usefulness of the help you've received would be nice.

Larry_from_LA · 03-10-2009, 11:05 AM

Macropod
Thank you very much for the suggestion.

I have never used a macro. Where should I place the macro?

**macropod** · 03-11-2009, 12:04 AM

Hi Larry,

1. With your Excel workbook open, press Alt-F11 to open the vbe (visual basic environment).

2. Select your workbook from the list of open workbooks on the left-hand side of the screen.

3. Choose Insert|Module.

4. A new vba module has now been created. If you don't see 'Option Explicit' at the top of right-hand pane, insert it.

5. Copy & paste the code I gave you below the 'Option Explicit'.

6. Press Alt-F11 again to return to your workbook

7. Select the range of cells you want to process (it doesn't matter whether they already have 'mailto:' or have hyperlinks - both will be added to all cells containing '@', denoting email addresses, if not already there.

8. Press Alt-F8 to open the macros window.

9. Click on 'E_MailFormatter' then 'Run. That's all there is to it.

Provided you save the changed workbook, all you'll need to do from now on if you add any more unformatted email addresses is the last two steps.

Larry_from_LA · 03-11-2009, 01:47 AM

macropod

Thank you, thank you, thank you.

What you instructed me to do worked perfectly. You have saved me hours of work!

03-07-2009, 06:30 PM	#1 (permalink)
Larry_from_LA Registered User Join Date: Mar 2009 Posts: 6 OS: Windows vista	Microsoft 2007 Excel I have an excel spreadsheet and I would like every entry in a specific column to be an e-mail address. Over 1,000 of the entries in this e-mail column are not formated for an e-mail address with the "mailto" preceeding the text. I currently have to right click on the cell, then when the drop down menu appears I click on "Hyperlink", then when the dialog box opens I have to copy what appears in the "Text to display:" area and paste it into the "E-mail address:" area. By doing this for every cell I get mailto:xxx@abc.com and a valid e-mail address. I have over 1,000 cells to which I am required to do the above process one at a time. Can the cells be converted to e-mail addresses in a more efficient manner?

03-10-2009, 05:34 AM	#2 (permalink)
macropod TSF Enthusiast Join Date: Apr 2008 Location: Australia Posts: 564 OS: Vista	Re: Microsoft 2007 Excel Hi Larry, Assuming you want to ensure both the 'mailto:' and the hyperlinking are applied to all cells with email addresses in a selected range, you could use a macro along the lines of: Code: Sub E_MailFormatter() 'macro created 10/03/2009 by macropod Dim oCel As Range For Each oCel In Selection If InStr(oCel.Value, "@") > 0 Then If Left(oCel.Value, 7) <> "mailto:" Then oCel.Value = "mailto:" & oCel.Value oCel.Hyperlinks.Add oCel, oCel.Value End If Next End Sub BTW, some feedback on the usefulness of the help you've received would be nice. __________________ Cheers macropod (MS MVP -Word)

03-10-2009, 11:05 AM	#3 (permalink)
Larry_from_LA Registered User Join Date: Mar 2009 Posts: 6 OS: Windows vista	Re: Microsoft 2007 Excel Macropod Thank you very much for the suggestion. I have never used a macro. Where should I place the macro?

03-11-2009, 12:04 AM	#4 (permalink)
macropod TSF Enthusiast Join Date: Apr 2008 Location: Australia Posts: 564 OS: Vista	Re: Microsoft 2007 Excel Hi Larry, 1. With your Excel workbook open, press Alt-F11 to open the vbe (visual basic environment). 2. Select your workbook from the list of open workbooks on the left-hand side of the screen. 3. Choose Insert\|Module. 4. A new vba module has now been created. If you don't see 'Option Explicit' at the top of right-hand pane, insert it. 5. Copy & paste the code I gave you below the 'Option Explicit'. 6. Press Alt-F11 again to return to your workbook 7. Select the range of cells you want to process (it doesn't matter whether they already have 'mailto:' or have hyperlinks - both will be added to all cells containing '@', denoting email addresses, if not already there. 8. Press Alt-F8 to open the macros window. 9. Click on 'E_MailFormatter' then 'Run. That's all there is to it. Provided you save the changed workbook, all you'll need to do from now on if you add any more unformatted email addresses is the last two steps. __________________ Cheers macropod (MS MVP -Word)

03-11-2009, 01:47 AM	#5 (permalink)
Larry_from_LA Registered User Join Date: Mar 2009 Posts: 6 OS: Windows vista	Re: Microsoft 2007 Excel macropod Thank you, thank you, thank you. What you instructed me to do worked perfectly. You have saved me hours of work!