An Excel Macro to Copy Cell Contents as Plain Text

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.)

  1. In a workbook, Go to the Developer tab, and click "Record Macro".
  2. In the Record Macro dialog box, under "Macro Name", enter "CopyPlainText" (with no quote marks).
  3. In the "Store macro in" box, pick "Personal Macro Workbook", and then click OK.
  4. Click Developer > "Stop Recording", and Excel will create your Personal Macro Workbook.
  5. Close Excel. You'll be prompted to save your Personal Macro Workbook.

Now you need to edit your new macro.

  1. In Excel, go to Developer > "Visual Basic" to launch the Visual Basic Editor (VBE).
  2. You can find your Personal Macro workbook in the Project Explorer pane on the left hand side. If you don't see it, go to View > Project Explorer.
  3. Double-click on the "VBA Project (PERSONAL.xlsb)" folder > Modules > Module1, and you should find the empty CopyPlainText macro that you recorded.
  4. Copy and paste the code from below into your macro.
  5. Close the VBA Editor and return to Excel.

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.)

  1. In Excel, press Alt+F8 to open the Macro dialog box.
  2. Select "PERSONAL.XLSB!CopyPlainText" from the list and click the "Options..." button.
  3. In the Macro Options dialog box, you will see a field for a shortcut key. Enter "g" (lower-case, with no quotes) in this field. This will set the shortcut to Ctrl+G.
  4. Click OK, and then Cancel to close the Macro dialog box.

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.)

Confession

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.

Valid HTML 4.01 Transitional   Valid CSS!