automated find/replace to embedded URLs

[docmchem]

I have a spreadsheet with thousands of hyperlinks, all of which need the identical text change to their URL. I know find/replace won't find text in a URL. Is there another way?

docmchem

 

[Fishboy]

I have a spreadsheet with thousands of hyperlinks, all of which need the identical text change to their URL. I know find/replace won't find text in a URL. Is there another way?

docmchem

Hi docmchem, welcome to the boards.

If VBA is an option there are ways to get this done. We will need more information however to be able to help you. Can you give as one or two example hyperlinks, let us know what text needs to be changed and what it needs to be changed to please? Without this it is all guess work on our behalf.

 

[docmchem]

Hi fishboy, thanks for the response and the welcome. All of the existing URLs match this pattern, http://szukajwarchiwach.pl/29/280/0/xx.1/yyyy, where xx represents a one or two digit number, and yyyy represents a one to four digit number. After the change, I want to look like http://szukajwarchiwach.pl/29/280/0/xx.1/yyyy#tabSkany, preserving the same values for x and y, that is, just appending #tabSkany.

Each URL (hyperlink) is in its own cell, all the cells are in the same column, and there is nothing else in the column except the column header in the top row.

"If VBA is an option..." How would I know if it's an option? I'm using Office 2013.

 

[Fishboy]

By VBA I mean using macros to automate the process instead of just using formulas.

To clarify your request, you just want to add #tabSkany to the end of each and every hyperlink in the column?

 

[docmchem]

That's right, except there's only one "n", #tabSkany

 

[docmchem]

I don't know what I was looking at, I thought I saw #tabSkanny in your post. Need new glasses! Sorry about that.

 

[Fishboy]

That's right, except there's only one "n", #tabSkany

Hi again docmchem,

Try the following macro and see if it does what you want. The fact you didn't know what "if VBA is an option" meant I am going to go through this is some detail to make sure you know what you are doing (my apologies if this is actually overkill).

1. Make a COPY of your workbook to test this out on.
2. In the COPY of your workbook, press Alt+F11 to open the VBA Developer window.
3. In the VBA Developer window, find the name of your workbook from the project pane running down the left hand side of the screen.
4. Once you have found it, right-click on ThisWorkbook and select Insert->Module
5. In the new window that pops up, copy and paste in the following code:

Public Sub Update_Hyperlinks()
' Defines Cell as Range
    Dim Cell As Range
' For each cell in selction
    For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
' If the cell is not blank then...
        If Cell <> "" Then
' Add #tabSkany to the end of the cell value
            Cell.Value = Cell.Value & "#tabSkany"
' Recreate hyperlink based on the new cell value
            ActiveSheet.Hyperlinks.Add Cell, Cell.Value
        End If
    Next
End Sub

6. Go back to the COPY of your workbook, go to the Developer tab at the top of the screen and click on the Insert->Button (Form Control) button (the first option in the Insert menu)
7. The mouse cursor will change from an arrow to a cross and will allow you to "draw" out a button (just draw a square or rectangle and let go of the mouse button)
8. In the new window that pops up, double-click on Update_Hyperlinks to attach the macro to your new button. The new window will close.
9. Ctrl+Click on the first cell containing your hyperlinks to select the cell.
10. Scroll down your page to the bottom of the data (where the hyperlinks stop) then Shift+Click on the last hyperlink. This should have selected all cells containing your hyperlinks.
11. Scroll back up to your macro button and press it.

If more hyperlinks are added in the future, simply select all of the cells that haven't already been updated and click the button again to add the #tabSkany suffix.

I hope this helps and hasn't confused you too much. If I have gone into too much detail as you already knew most of this, I apologise.

 

[Fishboy]

I don't know what I was looking at, I thought I saw #tabSkanny in your post. Need new glasses! Sorry about that.

It's OK, I did put 2 N's but spotted and edited it fairly quickly. Just not before you saw it apparently!

 

[docmchem]

I'll give it a shot. The detail is much appreciated, exactly what I needed, thanks.

 

[docmchem]

It worked! Had to tweak a thing or two first, though. I hadn't been clear that the cell values (display values) were not the full URL. I completed them first, using find/replace. Then your macro worked perfectly. Afterwords, I cleaned up the display values using find/replace again.