Bulk replace of links within a sheet

[Stavros P. Zanos]

Suppose I have a number of links like http://www.company1.com/link1.htm,
...link2.htm etc., one in every cell of a sheet column. Now I want to
replace a part of the full link, e.g. to make links point to
www.company2.com, and retain the rest of the link. Is this possible in an
easy way (i.e. through the Replace command)?

Thanks in advance,

---
Stavros P Zanos, MD
Dept of Nephrology
Papageorgiou General Hospital
Thessaloniki, Greece

[Norman Jones]

Dear Stavros,
Yes you can edit multiple links with Edit/Replace.
Regards,

Norman.
"Stavros P. Zanos" <sza...@otenet.gr> wrote in message
news:#1fD6esFCHA.2444@tkmsftngp05...

[Roger Govier]

Hi Stavros

You could use Edit=>Replace and in the Find What box type company1 and
in the Replace With box type company2 then click on Replace All

--

Roger Govier
Technology 4 U W98SR2 XL2K

[David McRitchie]

Hi Stavros,
It is not possible with Replace (ctrl+H) as you would
wipe out the underlying link. It is not clear if what
you see matches the underlying link. If it does
you can reestablish the links by reentering each cell
F2 then Edit, or you can use MakeHyperlinks from
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#makehyperlinks

The rest will give you an idea of how you might set
up something that works, and how to obtain the
link that you can't readily see.

Something like this may better be suited if you are
changing things frequently.
Advantage: complete visibility of what you are doing.
A1: 'http://www.company2.com/
A2: 'link1.htm
B2: =HYPERLINK($A$1 & A2,"[x]")

You can find the underlying link with a userdefined
function, and then make the result a constant with
copy (ctrl+c), Edit, Paste Special, Values

Function HyperlinkAddress(cell)
'object type links only, not HYPERLINK worksheet functions
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

You could write a macro to convert the underlying
links as follows this will just massively convert every
link on the page without regard to selection.
Color coded as follows
40 salmon tint, has link but was not changed
35 green tint, link and value changed (link matched value)
36 yellow tint, link changed, value restored
Disadvantage: can't see links, macro must match
Must specify exactly what links have as prefix http://www.abc
is not same as www.abc
i.e. you specified company2 without http:// prefix
this macro requires consistency

Sub MassLinkChange()
Dim hl As Variant
Dim rng1 As String
Dim OldLnk As String, NewLnk As String
Dim FromLnk As String, ToLnk As String
Dim OldLnkCell As String, OldLnkValue As String
FromLnk = http://www.company1.com/
ToLnk = "http://www.company2.com/"
For Each hl In ActiveSheet.Hyperlinks
OldLnkCell = hl.Parent.Address(0, 0)
OldLnkValue = Range(OldLnkCell).Value
OldLnk = hl.Address
Range(OldLnkCell).Interior.ColorIndex = 40 'unchanged
If UCase(Left(OldLnk, Len(FromLnk))) = UCase(FromLnk) Then
On Error Resume Next
NewLnk = ToLnk & Right(OldLnk, Len(OldLnk) - Len(FromLnk))
hl.Address = NewLnk
If UCase(OldLnkValue) <> UCase(OldLnk) Then
Range(OldLnkCell).Value = OldLnkValue
Range(OldLnkCell).Interior.ColorIndex = 36
Else 'changed link and changed display
Range(OldLnkCell).Interior.ColorIndex = 35
End If
On Error GoTo 0
End If
Next hl
End Sub

HTH, instructions to install see getstarted.htm on my site
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Stavros P. Zanos" <sza...@otenet.gr> wrote in message news:#1fD6esFCHA.2444@tkmsftngp05...

[Norman Jones]

Dear David,

Using Edit\Replace to amend links certainly works for me .
If , for example, I amend your website link and then change it back (e.g.
edit\replace it with twit; edit\replace twit with it) I can then click on
the link and be transported(profitably!) to your web pages.

Regards,

Norman.

"David McRitchie" <dmcri...@msn.com> wrote in message
news:e6#fCptFCHA.2280@tkmsftngp12...

[David McRitchie]

Hi Norman,
Did not work for me. I have Excel 2000 is your different.

B3: http://www.abc.com

ctrl+h
replace abc.com
with mvps.org/dmcritchie/excel/colors.htm

B3: http://www.mvps.org/dmcritchie/excel/colors.htm
with old link of http://www.abc.com
previous testing wiped out link entirely that was
created via a macro.

HTH,

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Norman Jones" <norman...@btconnect.com> wrote ...

[Dave Peterson]

I use xl2002 and had the same difficulty as David McRitchie.

I typed in www.abc.com (and excel saw it as a hyperlink)
I dragged down a few cells
Selected that range and try to replace www.abc.com with www.microsoft.com.

The value in the cell showed www.microsoft.com, but the hyperlink tip showed
www.abc.com.

And when I clicked on the link, I didn't go to the microsoft.com site.

--

Dave Peterson
ec3...@msn.com

[Norman Jones]

Dear David,
I was totally wrong - I apologise!

Unfortunately, my 'verification' test (amend the hyperlink using
edit/replace and then reverse the amendment using edit/replace) was
insufficiently rigorous. It appeared to work only because the hyperlink
remained unchanged despite its textual mutation.

Again, apologies.

Regards,

Norman.

"David McRitchie" <dmcri...@msn.com> wrote in message

news:enmwO9tFCHA.2636@tkmsftngp13...