Office & productivity software forum: How to ''convert'' hyperlink to text

Right click on the hyperlink and choose 'remove hyperlink'. Works in Word 2000 and Word XP, at least.

Kees

Jim,

http://msdn2.microsoft.com/en-gb/library/microsoft.office.interop.word.hyperlink_members.aspx shows that address is a property of a hyperlink, and http://msdn2.microsoft.com/en-gb/library/microsoft.office.interop.word.hyperlinks.aspx shows how to get the whole collection.

But I'd prefer to program this in Excel VBA, because it's somewhat easier to browse through the table in Excel. Is there anything in the needs of your application that dictate to use Word more than Excel?

In fact, I think you can program an Excel function address_of_hyperlink that returns the address of the hyperlink. Once you've got that, all you need to do is fill, say, F1 with the formula =address_of_hyperlink(A1) - assuming A1 contains the hyperlink - and drag it down.

All of this is rather standard. If you can't program VBA yourself, it won't be too difficult to find somebody capable of doing it for you. Of course, it's up to you if you find the problem worth to pay somebody 2 hours at $25 (student) or $75 (professional programmer) an hour to solve.

Hope this helps.



Kees

Jim,

I don't think there is such a built-in function. But building your own functions is rather straightforward, especially with google to help you.

http://www.ozgrid.com/VBA/HyperlinkAddress.htm for example shows a function that extracts an email-address from a mailto-link (''mailto:meandmyself@hotmail.co'') by replacing the ''mailto:'' part with an empty string. It can't be too difficult to write a comparable function that replaces ''http://'' with an empty string, even for someone who's ''afraid of VBA''. Just three or four lines and you're ready. In fact, copy, paste and edit 7 characters.

It was the first hit of a search for excel vba function hyperlink address, by the way.

Can you tell more about your problems with the ampersand in Excel? Does it happen in the URL or in the text displayed? Can you give an example?

Hope this helps.


Kees

Try opening the document in Word, selecting the whole table, then copy/paste to an empty Excel document. This shouldn't change anything, I think.

Is the & in the description shown or in the underlying url? Can you give an example?

Kees

Thanks Swisse,

Just ran it but not sure I'm doing it properly.
Getting an error on the third line "For j....
Should I be entering the current file name here - with complete path.

BTW: Does this macro REMOVE the hyperlinks? or does it allowed their respective paths to be displayed (That's what I want)/
Meantime, as I've been working on this project ALL DAY, I've got to get some sleep. Perhaps this will be clear(er) to me in the morning.
Thanks again for your help, Jim

I tried to send an update this morning but it never showed.
Bottom line: I was able to ''liberate'' the information I needed by utilyzing my patented Rube Goldberg approach.
I opened the HTML document in Word
Viewed in SOURCE format
Did a search and replace for the part of the initial WWW. address then replaced with some of the code which APPARENTLY causes some text to display
Saved as HTML Doc
Re-opened in EXCEL then
Saved in Tab Delimted format
Then re-opened the Text Doc and used wizard to (more-or-less format my column.
Manually fixed some columns that did'nt format exactly as anticipated (simple stuff)
Another find and replace to get rid of ''junk charaacters.
Then save as a ''regular'' Excel file.
The data is now in format that works for me.
------------
Sorry to express in such non-technical language but, it's the only way I was able to 'figger it.
Thanks for all the suggestions. I'm sure one (or more) of them would have been more elegant but, I'm not comfortable with VB programming.
Thanks again, Jim

Dim c As Range
For Each c In ActiveSheet.UsedRange.Cells
If c.Hyperlinks.Count > 0 Then c.Value = c.Hyperlinks.Item(1).Address
Next c

End Sub