Office & Productivity Software forum

General discussion

How to ''convert'' hyperlink to text

by JimGunther / October 20, 2006 4:30 AM PDT

I have a large Word document consisting of five columns. The first column is a list of Names each of which hyperlinks to an individual World Wide web address. 3 of the other columns are text and the fifth is also hypertext.

I need to see each associated Web address in Column 1 as TEXT. (I don't need to convert the other hypertext column.)

As a first step, I have duplicated (copied) the first column to a (new) column 6 so I can convert the contents of the column to text, so, www address is visible.

(BTW: I tried to Tools/Options/Show and checked ''Show Codes'' but it showed the codes for ALL the columns of data - I only need to convert the one (#6) to text.)

Discussion is locked
You are posting a reply to: How to ''convert'' hyperlink to text
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: How to ''convert'' hyperlink to text
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Convert hyperlink to text.
by Kees Bakker / October 20, 2006 6:33 AM PDT

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

Kees

Collapse -
Convert hyperlink to text.
by JimGunther / October 20, 2006 9:22 AM PDT

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.

Collapse -
This needs programming.
by Kees Bakker / October 29, 2006 1:23 AM PDT

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

Collapse -
Excel / show Hyperlink address?
by JimGunther / October 29, 2006 3:03 AM PST

Thanks Kees.

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

Collapse -
Re: Excel function
by Kees Bakker / October 29, 2006 4:47 AM PST

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

Collapse -
Excel function
by JimGunther / October 29, 2006 6:34 AM PST
In reply to: Re: Excel function

Thanks Kees,

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 "&amp:" 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.

Collapse -
Re: hyperlinks
by Kees Bakker / October 29, 2006 4:01 PM PST
In reply to: Excel function

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

Collapse -
Macro To Remove Hyperlinks
by swisse / October 29, 2006 7:50 AM PST

You can try this short macro, but it will convert all the hyperlinks in the document, maybe even in the other columns.
Sub NoHyperlinks()
Dim j As Long
For j = ActiveDocument.Hyperlinks.Count To 1 Step -1
ActiveDocument.Hyperlinks(j).Delete
Next
End Sub

Swisse

Collapse -
Remove Hyperlinks
by JimGunther / October 29, 2006 9:22 AM PST

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

Collapse -
Remove Hyperlinks
by swisse / October 29, 2006 4:45 PM PST
In reply to: Remove Hyperlinks

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.

Swisse

Collapse -
Well, I found a work-around.....
by JimGunther / October 30, 2006 11:54 AM PST
In reply to: Remove Hyperlinks

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

Collapse -
(NT) (NT) Glad to read you got it working. Good job.
by Kees Bakker / October 30, 2006 3:50 PM PST
Collapse -
this macro may save Jim effort
by beckonmailru / September 7, 2009 8:24 PM PDT

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

Collapse -
Using VBA in Access you can remove a Hyperlink with...
by socratesfoot / April 11, 2012 5:18 AM PDT

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...

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

FALL TV PREMIERES

Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!