Extracting hyperlink text from a 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 helpful 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 http://www.excelforum.com/excel-gene...link-text.html

The only trouble is, how and where do I use the Functions suggested in the other discussion? Should I set up a Macro and insert the above Function into it? Or is there a completely different way to do it? Any advice in how to do this in Excel 2003/WinXP is very welcome.

Thanks.

Excel Cruncher

Thanks for getting back to me. Sorry about the delay in responding - a long weekend and other work intervened.

In answer to your question, the columns involved vary from sheet-to-sheet and workbook to workbook.

For argument's sake, let's say on one of the sheets the hyperlink text is in Column J and I want to park the text from the hyperlink in Column N.

So, the next step is...?

Cheers,

Excel Cruncher.

Hello,

Thanks very much for this. It works very well, and will be very useful for other tasks I have to achieve, but it isn't quite what I need. Perhaps I wasn't clear enough in describing the problem? Sorry about that. Here's another try:-

The text of the hyperlinks I'm trying to extract and edit is the Underlying text, the actual Hyperlink, not the Displayed text. The Displayed text is useless, saying "Click Here" and other such rubbish. To extract that, I can use the TRIM function or your lovely macro which does it at lightning speed. I'm very impressed with that.

The Hyperlink itself - accessible by right-clicking on the hyperlink and selecting Edit Hyperlink, &c - can refer to web pages, worksheets, individual records, specific cells and a whole range of sources. That is the information I need to extract.

Is there any way I can do that?

Thanks for your time and effort, which is very much appreciated,

Excel Cruncher.