Converting hyperlink to text in MS Word and MS Excel?

[Cham-D!]

Sponsored Links

as the title of this particular thread is saying what I wanted to ask over here. I am having very big word document which is divided into five columns. This particular first column is having list of names and which should be hyperlinked. After that there are 3 column which are having text. Fifth and last column is having hypertext. Now I wanted to tell you about the requirement of mine. I wanted to associated Web address with every single entry of column 1 in the text. I don’t wanted to convert rest of hypertext column. Let me know if you are having any useful information to get the requirement of mine. thanks a lot in advance.

[Alfons]

After going through the requirement of yours I wanted tell you that you should simply right click on the hyperlink and simply select remove hyperlink. It supposed to work with Word 2007. So try it and let me know whether it is working for you or not.

[Cham-D!]

Thanks a lot for the prompt replies of yours over here. I think you are not able to get about my requirement. I wanted to simply convert blue text which is representing hyperlink into the actual address into Text. it should be like JonSmith.com. If I remove hyperlink the it would simply change the Name into blue color to black. I have already mentioned that all the data are in the column. Hence I am wondering that there should be write/translate option which write content to column 6 to column 7. There are numerous entries so it seems to be impractical edit all entries. Now tell me if you can help me out to get the requirement of mine. thanks a lot again.

[Gania]

In this particular situation I wanted tell you that address is property of hyperlink and it helps you to get the collection. Well you can get this particular thing by using VBA on MS Excel why I am saying that it seems to be easy enough when you are supposed to browsing through tables. Also you should know that you will be able to execute address_of_hyperlink function on Excel. It simply returns address of hyperlink. Once you have managed to get the same you have to fill address_of_hyperlink(A1) formula. Here A1 supposed to be hyperlink and it can be dragged. But still I am not aware of whether you will be able to make use of VBA on word documents of not.

[Cham-D!]

Again thanks a lot for prompt replies of yours. I know one will be able to lots of stuffs by having built in functions available into Excel as well Word. But there is no option available which was same as that of D-Base Command which allows you to get ROM POSITION F1 and it would return hyperlink and it supposed to appear into A-1. According my experience is concerned about word and excel, I found that Excel are simply modifying some characters into Hyperlink. That means & is getting change to amp. So I would need to edit the word file to make the thing working for me.

[GaganjyotTechie]

As far as I know there are not built in function available to get the requirement of yours. however you can create such function which will help you to full fill your requirement.
you can take an idea by using below mentioned function.

Function GetAddress(HyperlinkCell As Range)

    GetAddress = Replace _

    (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")

End Function

The above mentioned function is supposed to extracts email address and from mailto-link and replace the same with mailto: with having empty string. So I don’t think it would be a difficult thing to do write a comparable function which would replace http:// with empty string. If you scared of VBA then also you will require to edit couple of lines.

[Panop]

As I have already mentioned I am having thousands of WWW address into the WebPage format. It is getting edited fine on word without having any issue. however when I am trying to open the same into Excel sheet ‘&’ is getting converted into ‘&:’ hence I www not opening at all. it seems to be really frustrating for me as I used to sort all the things into Excel. I used to click on every single hyperlink in order to get access to individual address. Only solution which I can think of is that I have to sort it on Excel and open the address on MS word so that I can edit it and fix the same. I have done the same and I have found that sorted Excel file into HTML is converting into proper address and there were not extra "amp;" however if you are having any other solution to fix the above mentioned then let me know about the same.

[-Deven-]

I don’t know whether it work for you or not. You should launch word and try to open that document in MS word itself. Now select the entire table and copy abd paste the same to Excel sheet. I don’t think so you will get any issue. If it does not work for you than you should run below macro.

Sub NoHyperlinks()
Dim j As Long
For j = ActiveDocument.Hyperlinks.Count To 1 Step -1
ActiveDocument.Hyperlinks(j).Delete
Next
End Sub

it would convert all the hyperlinks available in the document. It would work on other column as well. It would work with hyperlink but not on the text. You should copy above mentioned Macro properly in order to avoid error. I am assuming that you are using Word Visual Basic Editor.