|
Display The Actual Link / Email Address From Links in Excel - UDF
Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the actual http address or the actual email address from cells that contain links. This udf is simple to use since you just have to put the cell reference that contains the link into the range argument and hit enter. The default text argument is optional and just displays whatever you want if the cell you point to does not contain a link.
This UDF is great when you need to turn a list of imported email addresses, imported links, or a web query that contains a lot of links from the displayed links to the actual underlying values of the links. You can also easily and quickly transform lists of email addresses that are in link form (with a "mailto:" prefix) into an email address in text form.
Where to install the macro: Module
UDF to Display The Actual Link / Email Address From Links in Excel
Function SHOWLINK(cell As Range, Optional Default As Variant)
'This UDF will display all links as text and display the full http address that is in the hyperlink
'This UDF also automatically detects if the link is an Email address and displays it correctly
If (cell.Range("A1").Hyperlinks.Count <> 1) Then
If IsMissing(Default) Then
SHOWLINK = "Not a Link"
Else
SHOWLINK = Default
End If
Else
If Left(cell.Range("A1").Hyperlinks(1).Address, 7) = "mailto:" Then
SHOWLINK = Right(cell.Range("A1").Hyperlinks(1).Address, Len(cell.Range("A1").Hyperlinks(1).Address) - 7)
Else
SHOWLINK = cell.Range("A1").Hyperlinks(1).Address
End If
End If
End Function
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hi this is my first post. I wasn't sure if this would be a macro or not, so I posted it here... hope this is the right location.
I am using Office XP Professional and Excel 2000. I have a document which has hyperlinks. Is there a macro or a way to make it show the actual email address johndoe@abc123.com instead of John Doe, President and having to click on it as link? Also, would there be a way, to keep it showing, John Doe, President (without linking) so I keep the person's information as well as get the email address? I will have hundreds if not thousands of these in my document, so I don't want to have to manually do it if at all possible.
Thank you very much for your time and help.
Hi,
Please help me with a question:
I would like to be able to convert email address hyperlinks to the actual email text.
For example, let's say Alex Rodriguez's email address is arod@yankees.net. If I have a hyperlink in an excel cell that reads "Alex Rodriguez" how do I convert it to read the actual email address in that cell that the hyperlink sends me to which is arod@yankees.net.
Further, if i have a list of 100 such hyperlinks, how can I convert them all in one swoop?
Thanks in advance.
I copy a list of names from one page and put them in a excel spreadsheet.
What happens is just the name show like DUSTIN MCALISTER but when you hold
the mouse over the name the email address shows up in a little yellow box
showing what the hyperlink is. Is there a way to make it show the email
address instead of the hyperlink?? That way I can click on a row of names and
all the emaill addresses will be there so I can copy and paste them into my
Email and mass email my clients? Thanks!
I have an .xls spreadsheet that has a column which contains email links that appears like this: Email
When you click the cell or hover it shows the email address. I would like to convert the entire column to actually display the email address. I need to export these email adddresses once I get them. The only way I have found that I can get the email address is to create a new column, then click the Email link and it opens up an outlook email. Then I have to copy and paste the email address into the new column. I have over 2000 cells in this column. Is there any way to convert this column to the actual email addresses that are embedded in it? Any help would be appreaciated!
I have forgotton the option to make the email address as underlined link. where it is so that I could uncheck it?
I've been copying data from a webpage including a link to email addresses that is titled "Contact Me". When I paste this into excel, Contact Me link is visible. Under that link is some java code that also contains the email address (on the website, when you click contact me, you get a pop up box in which you write your message). What I would like to do to is write a formula that will allow me to see just the email address in its own cell. So, I want excel to look at the details under the link, cut out all the java garbage, and give me a clean email address. Any suggestions? Thanks
EDIT - All the links look like this - javascript:$("hdEmail").value='joe@abc.com';createAgtPopup();
All I want to see is the email address it its own cell.
I have a sheet with over 50 email addresses
I would like the user to be able to click onto one of these addresses and be directed to an email template.
The intended use is to send a protest email to the users member of parliament objecting to a proposed bill.
thanks
Ted
I have spend many hours searching for an easy way of changing a columb
where the visible text is "e-mail" and it's a hyperling to some e-mail
addresses, that I can see if I move the curser over the cells. I have
found some solutions concerning macros but as I'm not used to working
with macros, this seems very difficult for me!
Anybody have an idea?
Maybe I need to buy a small programme, or is it free ware?
br/Jens
In my Excel spreadsheet I have a column to store the email addresses of my clients. Every time I click on the text of an email address, it automatically opens Outlook with a new message composed to that address. How do I stop this from happening? I don't even use Outlook for my email and this is really frustrating. I've tried changing the format of the column to no avail.
Thank you; your help is most appreciated.
how can I create a button (on the excel sheet) which will auto attach the excel workbook to an Outlook email with the To. address filled in with the email address it needs to go to
|
|
