Remove 'Symbols' When Importing

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

When I import pages from the internet into Excel, I often have a bunch of
Internet 'symbols' or 'links'.

Examples are: little tick symbols from a stockbroker which link to "trade
this stock" type pages, or stock symbols underlined in blue, which take me
to a stock chart.

These are fine on the internet page, but I don't want them in my Excel
table.

I know I can eliminate them by using 'Copy' and 'Paste Special/Values' to a
new page in Excel, but is there a simpler way to 'turn off' these things?


Steve
 
If you don't have any other shapes on that worksheet...

After pasting
edit|goto|special|objects
and hit the delete key on the keyboard

If they don't disappear, try clicking on the design mode icon on the control
toolbox toolbar (xl2003 toolbars), then do the edit|goto stuff again.
 
--
Steve
Dave Peterson said:
If you don't have any other shapes on that worksheet...

After pasting
edit|goto|special|objects
and hit the delete key on the keyboard

If they don't disappear, try clicking on the design mode icon on the
control
toolbox toolbar (xl2003 toolbars), then do the edit|goto stuff again.
Click to expand...

Dave,

Sorry for the late reply.
Your suggestion doesn't work in my case (maybe because I'm using Excel
2000?).

Being more specific, can I change some Excel formatting so that imported
stock symbols which appear in Blue and Underlined (like an internet link or
URL) are re-formatted as normal text?

Steve
 
I thought your symbols were objects--like little icons.

But you mean that they're stock symbols, right?

And you've already deleted the hyperlinks themselves, right?

If both are true...
Select the range
format|Style...
And change to Normal
(don't use Hyperlink style)

If you still have hyperlinks and you want to get rid of them:

Select the range that should have the hyperlinks removed
hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window
type this and hit enter:
selection.hyperlinks.delete
 
Dave,

I confused the issue.

1. I didn't know the name "hyperlink" so couldn't find how to remove them in
Excel Help.
Embarrassingly, I find I can use the 'Format Painter' to copy simple text
formatting to these hyperlinks and make them into text.

2. I do have additional 'check boxes' and one 'drop down box', which I can
neither delete nor even select.

Thanks for your help.

Steve
 
First, be careful. Changing the format/style of the cell won't remove the
hyperlink. It'll just make it so it doesn't look like a hyperlink.

Second, show the control toolbox toolbar.

In xl2003, it's View|Toolbars|Control toolbox. Click on the Design mode icon on
that toolbar. (Let your cursor linger over the icons to see the yellow tooltip
to find it.)

Then try selecting the checkboxes and dropdowns/comboboxes (and then hit the
delete key).
 
Dave,

Quite right, I didn't remove the hyperlink with my format painting, but cut
and paste special/values does the job.
I can remove individual "symbols" (drop down boxes and check boxes) using
your method with the Control Toolbox. But how do I remove 200 check boxes?

Steve
 
You could use a macro...

Option Explicit
Sub Testme()
'if they're from the Forms toolbar
activesheet.checkboxes.delete

'if they're from the control toolbox toolbar:
Dim myCBX As OLEObject
For Each myCBX In Activesheet.OLEObjects
If TypeOf myCBX.Object Is MSForms.CheckBox Then
myCBX.Delete
End If
Next myCBX
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Dave,

Quite right, I didn't remove the hyperlink with my format painting, but cut
and paste special/values does the job.
I can remove individual "symbols" (drop down boxes and check boxes) using
your method with the Control Toolbox. But how do I remove 200 check boxes?

Steve
Click to expand...
 
Back
Top