Extracting Hyperlink Text From Cell For Editing?

I have inherited multiple spreadsheets with thousands of hyperlinks in a single column, from all of which I need to extract the hyperlink text to audit it and edit it. The hyperlinks are only displaying less than useful words such as "Go To" "View" or "Click Here", for example.

Manually, I can right-click on the cell, select Edit Hyperlink, press Ctrl+End to select the entire link, press Ctrl+C to copy it, click on OK to close the editing dialogue, then paste it in the destination cell. That's easy, and good if you only have a few to do, but very tiresome if you have more than a dozen. Life's too short to do all those I need to do manually with a 29 day deadline!

I tried setting up a Macro to do it for me, but couldn't get it to work down a column and paste the result in the destination cell on the same row as each successive cell was located. I do very little Macro work, and I find Excel to be less than intuitive in this respect, so I gave up. (At least in Word you can see the whole step-by-step process in the Macro Editing window and easily fix it, if need be...).

One previous discussion, dating from 2006, supplied two answers. See [URL]

Should I set up a Macro and insert the above Function into it? Or is there a completely different way to do it? how to do this in Excel 2003/WinXP is very welcome.