How do I trash HTML links en-masse from a spreadsheet? (msExcel 2002)

[Shiperton Henethe]

Hi

Can anyone tell me how to remove a column of cells with hypertext
links in them? MS Excel2002.

It's driving me nuts!

I can remove hypertext links individually (right click and then
remove) but if I delete the column of cells that they are
in, the f***ing hypertext stays behind!

General point:
When I have pasted stuff in from an HTML page it seems
to be incredibly hard to get rid of this kind of crud.

Obviously I *could* create a new spreadsheet and paste
the stuff I want in or export the damned thing into
CSV or TXT and re-import. But this is all very tiresome.

Is there no way to paste contents of an HTML table into
a Excel spreadsheet and keep the basic table structure
but to leave out all the HTML and formatting?!!


Ship
Shiperton Heneth

 

[Ken Wright]

Couple of subs previously posted - One for a selection, one for entire sheet

Sub RemoveHyperlinks1()
'David McRitchie, misc, 2003-04-08, misc
Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
On Error Resume Next
cell.Hyperlinks.Delete
Next cell
End Sub

Sub RemoveHyperlinks2()
'Robert Rosenburg
On Error Resume Next
Selection.Hyperlinks.Delete
End Sub

RE getting the data, have you tried Data / Get External Data / New Web Query -
Paste in the URL and then choose the table you wish to import.

 

[Shiperton Henethe]

Couple of subs previously posted - One for a selection, one for entire sheet

Sub RemoveHyperlinks1()
'David McRitchie, misc, 2003-04-08, misc
Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
On Error Resume Next
cell.Hyperlinks.Delete
Next cell
End Sub

Sub RemoveHyperlinks2()
'Robert Rosenburg
On Error Resume Next
Selection.Hyperlinks.Delete
End Sub

Yikes this is some kind of Macro I presume.
I dont kno how to do macros...
Okay, well I've just had a go.
Yes it seems to work. Miracle!

Thanks!

RE getting the data, have you tried Data / Get External Data / New Web Query -
Paste in the URL and then choose the table you wish to import.

I just tried that but it failed miserably only putting in the first line...

Actually I may have solve the problem a simpler way.
Enter 1 into a blank cell.
Control/C to copy it
Edit >Paste Special > Multiply
and hey presto hypertext is trashed!


By the way do you know how I'd convert insert
today's date on a hotkey (or F-key)?
(Would be useful for ms Word too, me thinks)


Ship

 

[Shiperton Henethe]

Yikes this is some kind of Macro I presume.
I dont kno how to do macros...
Okay, well I've just had a go.
Yes it seems to work. Miracle!

Thanks!

Damn I now cant figure out how to get macro
to run in a new spreadsheet. I ran before okay...
I tried changing the security settings
Tools > Macro > Security > Medium (or Low??)
But a) this seems to apply globally
[which I may not want]
b) I *still* couldnt get it to run either way!

I can see that I need to open the .XLS that contains
the macro I need otherwise I cant see the macro...

But how do I make (just) the current .XLS
run (just) the macro I've recently created...

With thanks



Ship

 

[David McRitchie]

To install the macros see
Getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm
but then I noticed afterwards that you did get them to work again.

You will find macros similar to those mentioned on my buildtoc.htm page.

You also asked for a shortcut key to enter the date.

Ctrl+; (semi-colon) to enter the date
Ctrl+Shift+: (colon) to enter the time
to enter both use
Ctrl+; followed by space followed by Ctrl+Shift+:

Time is a fraction of a day, so including time does not
use up more space than date alone they are (floating point) numbers.