Change all occurences of text string to hyperlin (97)

Topic

New Reply

I have a reference document that I am finalising that has several (abot 100 or so) repeated text strings throughout the document ( text string “List #”) that I would like to make into a hyperlink to another Excel doc. as in {HYPERLINK “..ListsList Usages.xls”}.

Find & replace however does not operate with hyperlink fields.

Does anyone have a method that I could use to perform the replacement automatically, as I am not looking forward to manually applying the hyperlink.

Thanks in advance.

Reply | Quote

Replies

Is it exactly the same hyperlink each time?

If it is then you should create it once and then copy that to the clipboard.
Now use a search and replace but in the replace with box type in ^c
This will replace the found text with whatever is in the clipboard.

Reply | Quote

Thanks Andrew,

I tried that, but ^c will only paste the text, (ie ‘List #’) not the underlying hyperlink field codes.

I did not know that the repalce function would allow you to put text on the clipboard in this fashion.

Thanks, you (and the lounge) have saved me a lot of time.

Reply | Quote

Not sure about HYPERLINK, but with some field codes, toggling the display to view codes (Alt+F9) allows you to search and replace their contents. You might or might not need to start your “Find what” with ^d (if I am remembering the Field code code correctly).

Reply | Quote

Not sure about HYPERLINK, but with some field codes, toggling the display to view codes (Alt+F9) allows you to search and replace their contents. You might or might not need to start your “Find what” with ^d (if I am remembering the Field code code correctly).

Reply | Quote

Hi Kieran:
I’m not sure what you are saying. Did this solve the problem or not? If you copy an entire field to the clipboard, the replace function WILL replace found text or fields with the field that you’ve copied to the clipboard.

If you have text in the form of a filepath, you can use a combination of Find/Replace & Autoformat to create hyperlinks. This would depend upon a pattern in your filepaths. If, for example, all your filepaths were:

C:foldersubfolderfilename.xls

you could do a Find/Replace that changes it to:

“http://C:foldersubfolderfilename.xls”

After that, you could use AutoFormat as described in post 293172 to change them all to hyperlinks. Then you could remove the

http://

by using wildcards & doing
1. Find
(HYPERLINK )(“)http://(*”)

2. Replace:
123

Then update all the fields. If you need further explanation, please post back.

Reply | Quote

Thanks Phil,

Andrew did solve my problem at the first try.
The ‘trick’ was knowing that ^C in the replace field caused the clipboard contents to be used as the replacement text.
Thanks for the otehr tips too.

Reply | Quote

Thanks Phil,

Andrew did solve my problem at the first try.
The ‘trick’ was knowing that ^C in the replace field caused the clipboard contents to be used as the replacement text.
Thanks for the otehr tips too.

Reply | Quote

Hi Kieran:
I’m not sure what you are saying. Did this solve the problem or not? If you copy an entire field to the clipboard, the replace function WILL replace found text or fields with the field that you’ve copied to the clipboard.

If you have text in the form of a filepath, you can use a combination of Find/Replace & Autoformat to create hyperlinks. This would depend upon a pattern in your filepaths. If, for example, all your filepaths were:

C:foldersubfolderfilename.xls

you could do a Find/Replace that changes it to:

“http://C:foldersubfolderfilename.xls”

After that, you could use AutoFormat as described in post 293172 to change them all to hyperlinks. Then you could remove the

http://

by using wildcards & doing
1. Find
(HYPERLINK )(“)http://(*”)

2. Replace:
123

Then update all the fields. If you need further explanation, please post back.

Reply | Quote

Thanks Andrew,

I tried that, but ^c will only paste the text, (ie ‘List #’) not the underlying hyperlink field codes.

I did not know that the repalce function would allow you to put text on the clipboard in this fashion.

Thanks, you (and the lounge) have saved me a lot of time.

Reply | Quote