My website column will not work as links

A column of company websites contains a mixture of forms: some with https://, some starting www, some bare domains, and a few entries that are not addresses at all. Anything that tries to use them as links works for some rows and not others.

Why this happens

• A URL needs a scheme to be a URL. “northwind.co” is a domain name, and while a browser will happily guess https:// in front of it, most software will not, because guessing is not its job.
• Columns like this are almost always assembled by hand over time, from business cards, email signatures, and forms, so they carry every convention anyone used. There is no single find-and-replace that fixes them, because the correct edit differs per row.
• Mixed in with the format variation is genuine rubbish: an email address in the website column, a company name, “n/a”, or a domain with a typo in the suffix. These look similar to the format problems and are a different problem, because no normalisation fixes a wrong address.
• Adding https:// to everything is therefore the wrong instinct. It makes the malformed entries look well-formed, which converts a visible problem into an invisible one.

What to do right now

• Separate normalisation from validation. Adding a missing scheme is safe; deciding whether the resulting address exists is a different question and needs a different check.
• Look for the entries that are not addresses at all before you normalise, because afterwards they are much harder to spot.
• Do not test these by opening them one at a time. Check the shape of the value, and leave whether the site responds to a separate pass if you need it at all.

Stopping it on the next file

A url field normalises what is safe to normalise and refuses the rest. A bare domain has https:// added, so northwind.co and www.northwind.co both arrive as usable addresses, and the value is stored in one consistent form rather than in the four forms it came in. Anything without a dot in the host is refused and named, which is what catches the company names and the “n/a”s that had been sitting in the column looking like websites. What it does not do is check that the address responds, so a well-formed address for a site that no longer exists passes, and it should: that is a fact about the world rather than about the file.

The contact list template is a sheet with those rules already set, if you want to see the shape of one before building your own.