15 total posts
Convert hyperlink to text.
Right click on the hyperlink and choose 'remove hyperlink'. Works in Word 2000 and Word XP, at least.
Convert hyperlink to text.
Thanks Anyway Kees,
I may not have been clear.
I want to ''translate'' the current BLUE TEXT (representing the hyperlink) to the actual address in TEXT(i.e. www.JonSmith.com).
Simply ''removing'' the hyperlink results in the original ''Name'' to change from blue to black with the www. adress being obliterated.
As all the data is in Columnar form, I was hoping I could ''write/translate'' the contens of column 6 into text in column 6 or 7.
BTW: I have THOUSANDS of entries so manual editing isn't very practical.
Appreciate any other suggestions.
This needs programming.
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.
Excel / show Hyperlink address?
I'm afraid of VBA.
I'd like to do everything I do with the functions ''built-in'' to Word or Excel.
(i.e. shouldn't there be a formula, function or something like a D-Base Command that says FROM POSITION F1 ''return the hyperlink <underlying address> which appears in position A-1 here (F1))
As far as my preference for using Word vs Excel - Excel seems to ALTER certain characters in the Hyperlink <i.e. changes ''&'' to ''amp;''> so, just thought that doing my edit in Word word save a step.
Thanks again, Jim
Re: Excel function
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:firstname.lastname@example.org'') 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.
I have saved thousands of WWW, addresses in WebPage format.
They edit nicely in Word / Convert perfectly to HTML but, when I opn them with EXCEL "&" becomes "&:" and, so, the www address doesn't open.
Too bad because I'd like to be able to sort in EXCEL and go direct;y to the hyperlink to access each individual www. address. Instead, I would have to sort in EXCEL then re-open in Word to get the correct Hyperlink. Yes, when I save the sorted EXCEL file in HTML it "converts" back to the proper address - eliminating the EXTRA "amp;".
Weird but intellectually challenging.
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?
Macro To Remove Hyperlinks
You can try this short macro, but it will convert all the hyperlinks in the document, maybe even in the other columns.
Dim j As Long
For j = ActiveDocument.Hyperlinks.Count To 1 Step -1
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
This macro will remove ALL the hyperlinks, possibly even the hyperlinks in the other columns. But only the hyperlink not the text. For thousands of entries this is a fast one. Can you elaborate more on ''allow their respective paths to be displayed''?
I hope you copied the macro correctly because it functions well. Be sure that you didn't substitute j for another letter, because that will generate an error. I presume you ran it in the Word Visual Basic Editor and in the ThisDocument under you current Project.
Well, I found a work-around.....
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
(NT) Glad to read you got it working. Good job.
this macro may save Jim effort
Dim c As Range
For Each c In ActiveSheet.UsedRange.Cells
If c.Hyperlinks.Count > 0 Then c.Value = c.Hyperlinks.Item(1).Address
Using VBA in Access you can remove a Hyperlink with...
Given: A textbox with a name RecLink1
Dim textualLink1 as string
textualLink1 = RecLink1.Hyperlink.TextToDisplay
I suspect you can do something similar in Excel with the cell reference...