top urgent hyperlink paths

[Ramadan]

my excel sheet is a huge one and I have a column that has
a disply of the hyperlink of all the cells I mean any cell
linked with a path by hyperlink to the ceartain folder
as this \\servername\sharename\directory1\directory1
\filename.pdf
now what hapen is that the repeating of directory1
was happend and now all the links to files are not correct
as tha previous one .
the correct path is \\servername\sharename\directory1
\filename.pdf
now is there any way to delete the folder directory1 from
the path to all the cells which are a big number without
editing evreyone each .
second is there any way to make a new column that has the
hyperlinks pathes then to make replacement with find for
any word?
thanks alot for help.

 

[Lady Layla]

Find \directory1\directory1\
Replace \directory1\

: my excel sheet is a huge one and I have a column that has
: a disply of the hyperlink of all the cells I mean any cell
: linked with a path by hyperlink to the ceartain folder
: as this \\servername\sharename\directory1\directory1
: \filename.pdf
: now what hapen is that the repeating of directory1
: was happend and now all the links to files are not correct
: as tha previous one .
: the correct path is \\servername\sharename\directory1
: \filename.pdf
: now is there any way to delete the folder directory1 from
: the path to all the cells which are a big number without
: editing evreyone each .
: second is there any way to make a new column that has the
: hyperlinks pathes then to make replacement with find for
: any word?
: thanks alot for help.
:
:

 

[Paul]

my excel sheet is a huge one and I have a column that has
a disply of the hyperlink of all the cells I mean any cell
linked with a path by hyperlink to the ceartain folder
as this \\servername\sharename\directory1\directory1
\filename.pdf
now what hapen is that the repeating of directory1
was happend and now all the links to files are not correct
as tha previous one .
the correct path is \\servername\sharename\directory1
\filename.pdf
now is there any way to delete the folder directory1 from
the path to all the cells which are a big number without
editing evreyone each .
second is there any way to make a new column that has the
hyperlinks pathes then to make replacement with find for
any word?
thanks alot for help.

Use Edit > Replace > Replace All.
Find what: \directory1\directory1
Replace with: \directory1

 

[Tom Ogilvy]

Unfortunately, find and replace does not work on the address of a hyperlink.

As answered in WorksheetFunctions:

Sub changelink()

Dim hlink As Hyperlink
For Each hlink In ActiveSheet.Hyperlinks
If InStr(1, hlink.Address, "\data1", vbTextCompare) Then
hlink.Address = Application.Substitute( _
hlink.Address, "\Data1", "\Data1a")
End If
Next

End Sub

I made a slight modification to make sure it was case insensitive, but other
than that, it worked fine for me.

I changed the path information to match my hyperlinks of course.

 

[Deborah Bogan]

You can copy the info over to word, edit it and copy it
back.