Is there a way to format URLs so that values show as "[Text]," but still copy correctly?

[alexcr]

I have a column with URLs, but don't like the appearance of having URLs in this worksheet, so I'd like all the URLs to simply appear as "[Text]". (Choose your preferred word here.)

I know you can make the URL a hyperlink, and then edit that hyperlink's "text to display." The problem there is that if you then try to copy and paste these URLs to another app, they copy as the text, not as the underlying URL.

Similarly, instead of using hyperlink functionality – which I have no use for and prefer to avoid anyway – I tried custom formatting the URL cells using ";;;"[Text]"". But I ran into the same issue I just described; I'm then unable to properly copy and paste the underlying URLs into other apps.

Is there any solution here? I know one approach would be to simply format the URL cells so that the font is white, and thus the cells appear blank, but I'd prefer not to go this path, as I'd like to have something short and simple still appearing in these cells. But not the URLs.

 

[Anthony47]

I don't know whether it will work well in your environment, but try this way:
-format the URLs with that customized format
-copy this code to a stabdard vba module

Sub CopyCopy()
Dim ClipB As MSForms.DataObject
Set clipb = New MSForms.DataObject
clipb.SetText ActiveCell.Formula
clipb.PutInClipboard
End Sub

-using Menu /Tools /Reference ad a reference to Microsoft Form x.y Object Library ( this might be already set, if your workbook contains a userform or an ActiveX)
-return to Excel, press F8; select CopyCopy from the list of available macros; press Options
-in the shortcut box type Shift-c (ie uppercase "c"); complete the setting with Ok and Cancel

Now select a cell with a hyperlink; type Contr-Shift-c to run the macro; check that the clipboard contains the full url and you can paste where you need

If you simply need to set that value into a vba variable then the trick would be myVariable = Sheets(xxx).Range(yyy).Formula

Try...