remove html link from spreadsheet

Copy and paste "values'?

I've used that method, but it just seems to transfer the link to adjacent cells. ( ! )

The only thing that seemed to work cleanly, was to delete entire lines and rewriting new lines (from a copy of old lines in a text file, or from memory).

If I see a link migrating around a recent repair attempt, I dump the work and go to previous back-up (done frequently), and try again.

Also, without knowing where the links are, they have to be found in a >36K lines of data.

I want to clean out the whole sheet, as there must be residual html that has been missed.

RL

Can you export to a CSV file and do a text search for the unwanted HTML links?

Then import back into the Excel spreadsheet when you are done.

Think I see what you meant - do the whole sheet.

I think that's does it, in this case.

I usually try to avoid Excel's definitions of 'values'. I find text being assigned exponents, turned into dates or a 'proper' name with capitalized first letters, when no changes to the actual text was desired.

Hard to check large files for that.

RL

I'll keep that in mind for smaller files, in the future.

Passing potential entries through a text editor is my usual stripping method, but I'm working with two sheets at the moment - one riddled with dead links. None of the text values being compared/moved have links, but the mouse seems to pick some up, at times, anyways.

HTML usually shows up here as a mistake and is hard to track down, after the fact.

RL

I suppose the smarter thing to do is to also strip html out of the source file, to avoid future mistakes.

I hate compeeyoudahs.

RL

Maybe you are a victim of MS famous unreliability. I'd try another program, such as

1. Google Sheets:
   formatting link
2. OpenOffice:
   formatting link
3. Zoho Sheet:
   formatting link
4. ONLYOFFICE:
   formatting link
   Another possibility is a corrupted file in the operating system. Try chkdsk or Windows Defender.

I run Win7 and XP under VirtualBox in Ubuntu 10.04. This makes it easy to check for corrupted operating systems or programs. These do happen, even with NTFS, or maybe a program you downloaded has screwed up your system. If this happens, it is easy to copy the complete operating system and all your data files from a backup.

NB: if your backups are on the same hard disk as the originals, you have no backups if you lose the hard disk. This happens. Put backups on a separate hard disk or flash drive.

Other possibilities are a bad memory chip or motherboard. Memory checking programs are available online, but I don't know of any program that can check motherboards. You have to do that manually.

I have just gone through 6 AsRock motherbords, 2 Asus, 2 Gigabyte, and 4 of uncertain provenance. They had various failures from missing keypad keys, no sound, DOA, couldn't connect to the LAN, or other miscellaneous problems. I am down to one working motherboard, but it won't recognize other hard drives or CDROM reader on different SATA busses, so I am hosed if it dies.

I have more motherboards on order, but I have received news that they will be delayed due to COVID. I am keeping my finders crossed that the system will survive until the new motherboards arrive.

Pretty sure it's handled, for the meantime.

I've got a laptop, 2 desk, and 4 lab machines that have their own vintages, OS and requirements. Only one desk and the laptop are connected to the internet. If the lab machines need it, I boot them to their secondary Ubuntu OS, but that's once in a blue moon.

The spreadsheets get transferred to a second desk machine archive, the laptop and (eventually) a remote web server for public download.

All three of those machines get backed up to a portable drive. The most frequently used (desk) machine is on it's second motherboard - but only because a harness assembly to USB ports fooled me. The harness was the actual source of USB issues (had dud port-dedicated chip at socket). Killed two

32G memory sticks (with contents) in the meantime and gave bogus webcam issues.

The lab machines get duped to their own back-up for software issues. Two of them (dell) are on their second motherboards due to cracked connectors and one popped video processor. These machines work with equipment that can't see anything over 2G on a usb stick, and ancient serial ports.

Every additional machine makes me resent, despise and downright hate the whole bloody business. I recon they've stolen about

20yrs of my life with their bullshit. I could have been fiddling with other, possibly useful stuff, on the bench. It's all kibble, in the end.

RL

Sounds too complicated. You need to think about simplifying things. For example, I keep all my machines absolutely identical so any one can take over any time it is needed. This way, once I get the system running, it is easy to clone and make as many copies as I want, and I can run my shop from anywhere there is a LAN connection.

The nice thing about VirtualBox is you can keep identical byte-for-byte copies of the entire virtual drive and send them anywhere it is needed. Another nice thing is you can have multiple operating systems running simultaneously.

For example, I usually have WinXP, Win7, and Ubuntu all running simultaneously and can transfer files back and forth between systems as needed. XP is my main system, and Win7 is used to order stuff from Instacart. So I make up my shopping list in XP, then send it to Win7 to order. Meanwhile, Ubuntu is playing music in the background.

The reason I don't use Win7 as my main computer is it is noticeably slower than XP, so I only use it when I really have to, and that is on Instacart. All my other computers have the identical configuration, so they can take over any time if needed.

I do have a Thinkpad laptop that I'm trying to give away, but so far no takers. These are useless. Limited memory and disk space, and I can't figure out how to load my favorite programs on it. It's not even worth wasting the time trying to figure out how.

I'm running Win7.

Try right-clicking on the link, scroll to the bottom of the pop-up list and left click on Remove Hyperlink.

Let us know if that doesn't work.

One way to automate it fairly painlessly would be to record a macro whilst doing the action you want to automate and look what it produces. Most times you will get lucky but sometimes you get nothing at all.

Namely:

Sub Macro1() ' ' Macro1 Macro ' Selection.Hyperlinks.Delete End Sub

Selection entire sheets and run that macro and the hyperlinks are history

To apply it to every sheet wrap it in a for each sheet loop an you get:

Sub Zap() For Each s In Worksheets Debug.Print s.Name s.Hyperlinks.Delete Next s End Sub

I assume here that you have developer mode available to you

Options Settings Trust Centre Macros/VBA

Hope this helps.

That's an option in Excel 2010, but not in Excel 97. It was the first thing I tried - all sort of options but no 'remove' or to edit down to a blank space.

Seeing it migrate to adjacent cells set off all sorts of alarm bells; no option but to pull the plug and great suspicion that the same thing had occurred elsewhere, without my noticing it.

I've been restricting the target spreadsheet to the older version to retain full reverse compatibility, as 2010 doesn't seem to fulfil this promise when it 'saves as'. Perhaps I'm foolish in thinking that this is not just ignorance.

As long as it runs in 97, I'm assured that everyone has access to it, when distributed. So long as at limbos under the 64K line limit, this is still a practical precaution. After that, it's Open Office, but I don't look forward to the migration.

RL

Actually it's not as simple as that. In Excel 97, the 'edit' option on rt-click for hyperlinks actually does have a 'remove hyperlink' button.

This doesn't work - in fact it actually causes the hyperlink to be associated with the entire column, without any visible indication. This is retained after the original linking cell is deleted.

I'm not sure what action causes further migration to adjacent rows, empty or otherwise. Presence is only visible on mouse-over.

RL

I feel your pain.