Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change all occurences of text string to hyperlin (97)

    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.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Change all occurences of text string to hyperlin (97)

    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change all occurences of text string to hyperlin (97)

    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.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Change all occurences of text string to hyperlin (97)

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

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change all occurences of text string to hyperlin (97)

    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>post 293172</post#> 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.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change all occurences of text string to hyperlin (97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •