Extracting http:// address from text hyperlinks

Hi Gdelrio3,

You can try this for a "quick" fix:

If your text hyperlink addresses are in column A (A1 downward), then in B1 put the formula:

=HYPERLINK(A1,A1)

Fill that down as many rows as you need. You can then copy column B and PasteSpecial->Values over column A. Then you can delete column B.

You could also use VBA to update the cells in-place (not using a helper column). The code example below does just that (assumes text links are in column A & code attached to the worksheet):

Please Login or Register  to view this content.

Originally Posted by Gdelrio3

Hello everyone,

I'm working on a huge report and need to convert text links to actual http:// addresses.

My painful and manual process has me:
-right clicking cell with hyperlink text
-Select Edit Hyperlink
-Copy http:// link from address bar
-Exiting prompt
-Pasting address into cell and moving on again

If you know of an amazing shortcut, I would love to hear it.

Needed end result:
Hyper Link Text of Website --> www.hyperlinktextofwebsite.com
Thanks in advance for your help!

Gabriel

Double click the cell which contains text and press Enter, if it turns text into hyperlink then following tip should work.

Press Ctrl+H
Find what: www
Replace with: http://www
and press Replace All