Help neeeded with an obscure Excel problem

Philocalist

Regular member
Joined
Dec 11, 2004
Messages
2,563
I've just been gifted (most of) a (slightly broken) database - basically, a very large Excel spreadsheet listing several hundred different fishing reels, where pertinent details are listed along each row, in the various columns.
One of the columns currently contains what were once active hyperlinks for each reel - on the original installation, hovering the mouse over a hyperlink would cause a large thumbnail image to appear in a pop-up window - clicking on the link would cause the picture file to open properly in whatever software was specified.

Understandably, those links are no longer working, as when the pictures were moved - to my PC - the pathway was changed, naturally.

All of the images, correctly named, still exist in a folder but for various reasons, I'm unable to locate the folder anywhere in a way that would allow the current hyperlinks to find them.
Does anyone know a way that I can change these links as a batch, rather than have to do each one of several hundred, individually - the simplest solution would be if I could somehow automatically prefix each hyperlink.

To try and simplify - assume the current hyperlink reads something like
{{{ Pictures }}}\My Reels\Selection\Resizes\ABU 506_IMG_0020.JPG

and what I want to end up with is, say
PC/DATA/NEW/{{{ Pictures }}}\My Reels\Selection\Resizes\ABU 506_IMG_0020.JPG

or, for that matter
PC/DATA/NEW/Reels\Selection\Resizes\ABU 506_IMG_0020.JPG

Any help very much appreciated!
 

Ben Field

Regular member
Joined
Sep 16, 2014
Messages
308
I’ve done a lot of similar things lately. For a lot of coding I do, I use a simple text editor called Crimson Editor. In excel I just highlight every cell in that column, paste it in to Crimson Editor, use the Find & Replace tool to change the first part of every URL in to the new one, then paste them back in. Excel is clever enough to pop the rows back in order. Wordpad(?) may do the same. Or any plain text editor with a find and replace function.

You can do it in Excel too by (in a new column) adding the first part of the new URL, then you’d use the SUBSTITUTE function to cut the first (old) part out of the URL, to give you the new one in full.

(having difficulty typing an example on my mobile)

Something like:

=“PC/DATA/NEW/Reels\Selection\Resizes\”&SUBSTITUTE(A1,”old-url-upto-filename”,””)

where A1 is the cell that the old URL sits in.

with a new column full of correct URL’s though, you could then highlight them all, copy, then paste in to the existing column, doing so as unformatted text (so that it doesn’t paste in the equations). You only want the results 😊
 
Last edited:

Simon R

Regular member
Site Supporter
Joined
May 19, 2002
Messages
12,519
"Find & Replace" appears to be present in my version of Excel - although mines an ancient version and when Microsoft release new versions of Office they don't always retain the most useful features.


1611627795782.png

Under the find & select tab is the replace tool and it appears to do what you need it to do

Simon
 

Dave

Red Leader
Staff member
Site Supporter
Joined
Aug 8, 2001
Messages
65,933
1611650837110.png

Make a copy of your Excel file before you start and work on the copy just in case it doesn't work and you can't roll back the changes.
 

Philocalist

Regular member
Joined
Dec 11, 2004
Messages
2,563
Can't get my head around this :) - Not sure what is happening, and why. The Find / Replace worked well, basically correcting the pathway / link where the picture for an indivdual entry could be found, and they all display / identify as being 'links', i.e. when clicked on, a small image should app;ear - except it doesn't - I get a message 'Cannot open specified file'
If I copy the text from the cell, then right-click and choose the option to edit the hyperlink, it fixes it after a fashion, but I can't work out why - at the edit stage, all I am doing is replacing the existing text in the box with the IDENTICAL text that I've just copied from the cell, then OK - and the link will now work as expected, despite nothing at all being changed, apparently.
Anyone have any idea what is happening here please - and how I might be able to effect this 'repair' as a batch, rather than line by line (a few hundred times!)?
 

spanky

Irregular Member
Site Supporter
Joined
Aug 28, 2007
Messages
10,316
Make a copy of one cell. Then fix the copy. Then see if the cells are the same (in another cell do =c3=d3 for example) if true then they are the same, if false then their may be subtle differences (leading, trailing space etc). Then fix the subtle differences!
 

muskrat

Havin' a Waggle
Site Supporter
Joined
Oct 26, 2004
Messages
7,375
I'm not sure if this is relevant, but in the OP and subsequent replies the link has a mixture of forward and backward slashes. I would have thought that if it was a link to a file it should be backslashes, and if true http hyperlinks it would be forward slashes.

Are you able to provide some screenshots of the cell content?
 

spanky

Irregular Member
Site Supporter
Joined
Aug 28, 2007
Messages
10,316
If you don't mind creating another column then the following will work:

1612382742663.png

A4 is the source data, B4 is the output, the text above is the formula. I notice the slash between NEW and My Reels seems to be different in your original to my version.

One it's working you can always copy and paste special> value over the original references is you need to maintain the layout. But I'd definitely start this way so you can do it by formula without changing the original text until all is working.
 

muskrat

Havin' a Waggle
Site Supporter
Joined
Oct 26, 2004
Messages
7,375
Here's another thought - Hyperlinks have two components: The text that is displayed in the cell, and the address of the hyperlink that is hidden from view.

I believe using Find & Replace will only update the text.

Can you right-click one of the cells and choose Edit Hyperlink. You should see a dialog box with the text and the address.
 

muskrat

Havin' a Waggle
Site Supporter
Joined
Oct 26, 2004
Messages
7,375
Similar to what @spanky suggests you could create a column of new links like this:

1612450680002.png

Cell A1 has the original link and cell I1 has the formula as shown in I3 which creates the new link.
If that works then you can just copy I1 down the column.

I'm still not happy about those slashes going both ways though. It doesn't look like a valid link to me.
 
Top