by Rob Locher
(This article was written on 2024-07-31.)
A while back I wrote an Excel macro to copy the contents of a cell as plain text, which I found to be tremendously useful. Most of the time I want to copy a cell to paste it out of the spreadsheet, I just want the contents of the cell and not the font and color. This tool should be built into Excel, but it isn't. Thankfully, Excel lets you extend its functionality with macros. I have my macro bound to a "hotkey", in my case Ctrl+G, so I can easily use it. Read on to see how I did it.
A macro must be in a workbook (spreadsheet). Normally a macro only works inside its spreadsheet. Fortunately, there's an exception to the rule: if you put a macro inside a special workbook, the "Personal Macro Workbook", then it will work across all workbooks that you open on that computer. That's because the Personal Macro Workbook opens in the background, if it exists, whenever you run Excel.
To get started, you'll need to use the Developer tab in Excel, which isn't shown by default. If you don't see it, click File, and then Options. "Under Customize the Ribbon" and under "Main Tabs", tick the Developer check box. You should now be able to see the Developer tab.
To create your Personal Macro Workbook, the easiest way is to record a macro that does nothing. (Hat tip to Microsoft for this page, where I learned this trick.)
Now you need to edit your new macro.
Here's the macro:
Sub CopyPlainText() Dim cellText As String ' Get the plain-text content of the selected cell cellText = ActiveCell.Value ' Create a new DataObject to interact with the clipboard Dim DataObj As Object 'Set DataObj = CreateObject("MSForms.DataObject") 'doesn't work Set DataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' Set the text to be copied to the clipboard DataObj.SetText cellText DataObj.PutInClipboard ' Optional: Display a message box indicating success 'MsgBox "Plain text copied to clipboard!", vbInformation End Sub
Finally, you'll want to assign the macro to a hotkey, such as Ctrl+G. (By default Ctrl+G launches the Goto dialog, which can be used to navigate to a named range. I have never used that dialog since I started using Excel in 1991, so I don't mind using Ctrl+G for something else.)
Now, whenever you press Ctrl+G in Excel, it will execute the macro and copy the plain text from the active cell to the clipboard. Hooray!
If you want to make this trick work on another computer, an easy way is to
copy your Personal Macro workbook, which can be found here:
C:\Users\(user name)\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB .
(Windows Explorer and scripts will both recognize
"%UserProfile%\AppData\Roaming\Microsoft\Excel\XLSTART" for the
path.)
OK, time for a confession. I did write this macro twenty years ago, but I lost it. I wanted to recreate it, but I wasn't looking forward to spending an hour or two relearning how to do so. A coworker suggested I ask ChatGPT to write the macro, and tell me how to bind it to Ctrl+G. I had thought that ChatGPT is a mere curiosity. Well, I was wrong. I went to chatgpt.com and typed "Write a macro to copy only the plain-text contents of a cell, and bind it to Ctrl+G", and bam! ChatGPT did it, in less than a second, without my even logging in. I forgot to mention Excel, but it knew what I meant.
Unfortunately, the code didn't work. The same coworker suggested Claude.ai. After creating a login, which required giving it my email address and mobile phone number, I typed the same thing there. Claude generated code that worked. (The macro on this page is a combination of the two results.)
Wow. Wow! Mind blown. Everything has changed, people: we now live in a new era, the era of AI. The Internet was a pretty big revolution, but I think this could be bigger. Those of us who are techies, we must get used to the idea, and learn to use AI ourselves, if we are to stay relevant. Again, wow.