Excel 2013 now autoformats urls as hyperlinks when typing but not when pasting

Question

I'm using MS Office Pro Plus 2013. I have an Excel spreadsheet where I store hyperlinks to websites.

Up until yesterday (25 Aug 2020), I could copy a link from the address bar at the top of my browser (Chrome) and click (once) on a cell in Excel and paste it there, and it was autoformatted immediately as a hyperlink.

Today, with the same actions, the autoformatting does not work. If instead I double-click in the cell and paste the link and press Enter / Tab, it will autoformat, but I don't want to have to do these extra steps for every link I copy.

In File | Options | Proofing | AutoCorrect Options... | AutoFormat As You Type, the option "Replace as you type" | "Internet and network paths with hyperlinks" is checked. I take it that's why when I edit a cell (by double-clicking), it does the autoformatting, because it considers that to be typing. But pasting is treated differently. I found a page that applied to MS Word, not Excel, which said there were two places this option was set, but in Excel I don't see another place to set such an option that might apply to pasting.

If I remember right, it was last night that Windows 10 did an update on my laptop, which may or may not be relevant. Other than that, nothing has changed.

Does anyone know how I can get the previous behavior back?

Bruce

Answer 1

As far as I this behavior is existed for long time. Try to use this code with short cut key:

Sub test()
Dim cell1 As Range
For Each cell1 In Selection
cell1.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=cell1.Value
Next
End Sub

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

BTW, here are two easy workarounds (if you really hate double-clicking for now).

1. If you are on a blank cell already (but have not double-clicked into it, for instance just after pressing Enter/Tab or an arrow key), you can instead use the hyperlink hotkey (Cmd-K on a Mac, Ctrl-K on Windows) and the insert hyperlink dialogue box will appear with the cursor already in the URL field. THEN you press paste (Ctrl-V, Cmd-V) and then Enter and your hyperlink will be in the active cell. However, your cursor will still be on the active cell, so you'll need to press Enter or Tab to get to the next cell. However, the benefit here is that you can avoid touching the mouse (I tend to assume you are Alt-Tabbing to another application and grabbing these URLs, perhaps your browser, and therefore if you're pretty decent with your browser hotkeys, you can avoid the mouse maybe altogether. Either way, even though it's few more keystrokes, no double-clicks (and you could record a quick macro for this if you do this often enough - I would!).
2. If you don't need the URLs to be hyperlinked unto you are all done, a faster way with even less keystrokes would be to just paste them as plain text (as was already happening for you) and then use the Hyperlink formula to convert them. So, if you had a slew of URLs in column A, for instance, you would use the formula =HYPERLINK(A1,A1) in cell B1 and then auto-fill that down the B column (double-click the lower-right corner of that formula box). Then, you'll have a list of properly formatted hyperlinks to all your cells. You can then just delete column A.

Hope that helps!

Answer 3

Found the solution here, first method:

https://www.exceldemy.com/convert-text-to-hyperlink-in-excel/#1_Activate_Automatic_Hyperlinks_Option_to_Convert_Text_to_Hyperlink_in_Excel

Answer 4

Hi @Bruce Wiebe ,

This answer of mine won't thrill you (and I suspect you're absolutely right about a change in behavior), but in Office 365, I've only been able to paste clickable hyperlinks in two ways. The third way, which you describe as simply leaving your cursor on the cell, has never worked for me.

But if I double-click in the cell and then paste, or if I simply paste in the cell's value box in the formula bar, then it does turn into a hyperlink.

I've found it slightly annoying at times, but in general, I've tended to look at it as a way of cutting and pasting URLs (which I do a lot) as plain text for when I need that ability. Else, Excel would always auto-format as a hyperlink (if that setting was checked) and when I occasionally need it not to be a hyperlink, I have to go through the additional hassle of clearing the formatting on that cell.

FWIW, I'm using the latest version of Excel in Microsoft 365 and AFAIK this has always been the behavior of Excel. However, I'm going to follow this thread as I'm curious if there are some workarounds I've not yet thought of. Thank you for bringing this to our attention. In theory, we shouldn't need to use VBA just to get a bunch of text-based URLs to be hyperlinked.

Answer 5

excellent write up...i had been wondering this; i could hardly believe it; gmta