×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Changing part of a link for multiple cells in Excel
2

Changing part of a link for multiple cells in Excel

Changing part of a link for multiple cells in Excel

(OP)
Is there a way to update one cell and it updates/change part of the other cells at once?

I have a list of downloadable files on a special system and it does not have an html index file.
I myself can easily create one and upload it but clients that have the system cannot so I thought maybe Excel can help out.
I have them enter the IP address in one cell and it updates all the links.

This would be the one cell I would like to just enter the IP address:
192.168.1.200

This would be the list of links where I wwould like the IP address's to be updated by the above cell:
http://192.168.1.200/files/filename1.exe
http://192.168.1.200/files/filename2.exe
http://192.168.1.200/files/filename3.exe
http://192.168.1.200/files/filename4.exe

---
So I just change:
192.168.1.200 to 10.10.70.80

and end up with:
http://10.10.70.80/files/filename1.exe
http://10.10.70.80/files/filename2.exe
http://10.10.70.80/files/filename3.exe
http://10.10.70.80/files/filename4.exe

Thanks

________________________________________


=----(((((((((()----=
www.curlycord.com
Toronto, Canada

Add me to LinkedIN

RE: Changing part of a link for multiple cells in Excel

Assuming your IP address is always in cell A1, and your links start in cell A2 and go down without any empty cells, run this ChangeLinks macro:

CODE

Option Explicit

Sub ChangeLinks()
Dim intR As Integer

intR = 2
Do While Range("A" & intR).Value <> ""
    With Cells(intR, 1).Hyperlinks(1)
        .Address = Replace(.Address, Split(.Address, "/")(2), Cells(1, 1).Value)
        .TextToDisplay = Replace(.TextToDisplay, Split(.TextToDisplay, "/")(2), Cells(1, 1).Value)
    End With
    intR = intR + 1
Loop

End Sub 

I am sure somebody can come up with more elegant way to do it.... smile


---- Andy

There is a great need for a sarcasm font.

RE: Changing part of a link for multiple cells in Excel

Probably simpler just to use a worksheet function ..

So you have a cell with your IP address (and have named it IPAddress), then a table with all the file names and a column for the URLs. In the URL column the formula you want is (assuming filenames - as in /files/filename1.exe - are in column B)

=HYPERLINK("https://"&IPAddress&B2)

Just copy that down the table, et voila.

RE: Changing part of a link for multiple cells in Excel

(OP)
I have not dabbled in code or macros with excel yet but a star for both your efforts.

I have complied a sheet using strongm's method in this case.

I have a cell in column B with the name as IPAddress

I have all the file locations in column C
/files/filename1.exe
/files/filename2.exe
/files/filename3.exe
/files/filename4.exe

I have hidden column C

I have hidden the Row that contains "Column1" header (or table name?) with the arrow drop down.

All works great but I would like:
- To show just the name of the software (text that I choose, not the file name) instead of the actual full web link.

Doable?

Thanks!





________________________________________


=----(((((((((()----=
www.curlycord.com
Toronto, Canada

Add me to LinkedIN

RE: Changing part of a link for multiple cells in Excel

Quote (curlycord )

text that I choose, not the file name

And where on the sheet is this text?

You can add it to your Formula as a [friendly_name] parameter:
=HYPERLINK("https://"&IPAddress&C5, "Click Here")

and if your chosen text is in column D:
=HYPERLINK("https://"&IPAddress&C5, D5)

---- Andy

There is a great need for a sarcasm font.

RE: Changing part of a link for multiple cells in Excel

(OP)
I just used your previous solution before the edit:
=HYPERLINK("https://"&IPAddress&C5, MID(C5, 14, 50)
Where in the brackets:
C5 = /files/filename1.exe (the column that I have hidden)
8 = the f which is the first letter of filename1.exe
50 = allows for any file name up to 50 characters.

As for the new edited post:
=HYPERLINK("https://"&IPAddress&C5, "Click Here")

I tried that too and works (and will stick with that)
The only thing with that is it renamed all cells (file links) to the same name but I was able to edit one cell after the other pressing "tab" to move to next cell after each edit.

"=HYPERLINK("https://"&IPAddress&C5, D5)"
I will try this later

Mission accomplished!
Thanks again

________________________________________


=----(((((((((()----=
www.curlycord.com
Toronto, Canada

Add me to LinkedIN

RE: Changing part of a link for multiple cells in Excel

I was playing around with the =HYPERLINK() function, but realized you don't want the file name as a [friendly_name] in your HYPERLINK, tat's why I edited my reply.
But, as you can see, all of these attempts work (I hope) smile


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close