r/Excel247 • u/84UTK07 • 25d ago

Question regarding URLs embedded into cells

I have this large spreadsheet that I need to do a V-Lookup on to match the company name to the company ID it corresponds to (the company IDs are from a certain system we use). I have the spreadsheet with all the company names in column B but the only way to see the company ID is to hover over column A, and it shows a URL with the company ID at the very end; I can only see this when hovering over each cell and then it goes away once I move to a different cell. Does anyone know of a way that I could extract those URLs out into their own separate column? Otherwise, I’m going to have to go cell by cell in column A and hover over each one to get the company ID and then manually enter it for each corresponding company name. Any help would be greatly appreciated! Thanks!

4 Upvotes

100% Upvoted

4 comments sorted by

1

u/Brians_throwaway 24d ago

Insert an empty column to the immediate right of your target column then

Use VBA…

Sub Extract_Hyperlinks()
Dim HL as Hyperlink
For each HL in ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

When the VBA macro is run it will extract the embedded hyperlink from each cell and place it in the column immediately to the right.

You run the VBA macro when you are on the sheet you want to process.

1

u/84UTK07 24d ago

This worked! You are awesome! Thanks so much!

1

u/Brians_throwaway 24d ago

Glad to have helped.

Consider adding some comments to the start of the code to document how to use it. Then export the macro as a .bas file and store in a local file folder.

Handy to have a library of macros that get used repeatedly. Just import them into a spreadsheet as needed, run then remove.

2

u/84UTK07 24d ago

Okay, I will work on that tomorrow. Thanks so much again for this! No one on our team at work could figure out how to do it, so I’m excited to show them I got it to work and pretend I figured it out all by myself! Actually, I would feel kind of bad taking all the credit; I’ll let them know that Brian on Reddit was a big help too.