How to copy only plain text of cells in Excel?

Question

I am designing an Excel worksheet where the user will click a command button which copies a predetermined range of cells. The user would then paste the contents into a web app using Firefox or IE. The design of the web app is out of my control and currently the text boxes that are used for data input are rich text inputs. This causes the text to look odd and formatted like Excel when the user pastes into them.

Is there a way in Excel using VBA to copy only the plain text of the cells that are selected? No formatting, no tabling or cell borders, just the text and nothing else. My current workaround macro is copying the cells, opening Notepad, pasting into Notepad, and then copying from Notepad to get the plain text. This is highly undesirable and I'm hoping there's a way to do this within Excel itself. Please let me know, thanks!

Trustworthy

I wish this was possible. Every time I paste test from Excel into a Github issue, it uploads it as a picture, meaning I also have to do that intermediate paste and recopy. – Viki

@stuartd, it is possible. Check out my answer: https://mcmap.net/q/1640591/-how-to-copy-only-plain-text-of-cells-in-excel – Anfractuous

Answer 1

Something like this?

Sheet1.Cells(1, 1).Copy
Sheet1.Cells(1, 2).PasteSpecial xlPasteValues

Or

selection.Copy
Sheet1.Cells(1,2).Activate
Selection.PasteSpecial xlPasteValues

Copy copies the entire part, but we can control what is pasted.

Same applies to Range objects as well.

EDIT

AFAIK, there is no straighforward way to copy only the text of a range without assigning it to a VBA object (variable, array, etc.). There is a trick that works for a single cell and for numbers and text only (no formulas):

Sub test()
    Cells(1, 1).Select
    Application.SendKeys "{F2}"
    Application.SendKeys "+^L"
    Application.SendKeys "^C"
    Cells(1, 3).Select
    Application.SendKeys "^V"
End Sub

but most developers avoid SendKeys because it can be unstable and unpredictable. For example, the code above works only when the macro is executed from excel, not from VBA. When run from VBA, SendKeys opens the object browser, which is what F2 does when pressed at the VBA view :) Also, for a full range, you will have to loop over the cells, copy them one by one and paste them one by one to the application. Now that I think better, I think this is an overkill..

Using arrays is probably better. This one is my favorite reference on how you pass ranges to vba arrays and back: http://www.cpearson.com/excel/ArraysAndRanges.aspx

Personally, I would avoid SendKeys and use arrays. It should be possible to pass the data from the VBA array to the application, but hard to say without knowing more about the application..

Suave

Yeah, that's the problem. I know I can get the plain text values when pasting, but I won't be pasting into Excel. I need a way to copy the plain text values initially because the text is pasted into another program :/ – Trustworthy

Do you mean the text copied to memory? A better workaround than pasting to notepad would be to paste values to a hidden sheet and copy from there. Is copying into arrays an option? – Suave

I've never copied into arrays before. How would that work/help? – Trustworthy

I am not sure I can help more without knowing details about how VBA interfaces with the Web application. For instance, how do you currently input the data to the application from Notepad? – Suave

Oh, the VBA doesn't interface with the web app at all. The user manually pastes the text into the app using the browser. – Trustworthy

Oh OK, I see. I think the best solution in this case, will be to make a macro that: (i) copies the selected range (ii) pastes values only in a hidden sheet and (iii) copies the pasted range to keyboard. The sheet can be "very hidden", i.e., accessible with vba only. If you assign Ctrl+c to that macro, the user will think that he copies the range, but actually it will be your macro that runs. – Suave

note that if you assign your macro to an existing shortcut, this will be rendered unusable for all open worksheets.. I would choose a different shortcut, and let the user know (eg with a msgbox). – Suave

Answer 2

Actually, the best way to do this is to copy the cells and paste into a notepad. Notepad won't recognize the cells. You can then copy the text back into whatever cell you want. This works for copying text from multiple cells into a single cell.

Brevier

simple and elegant!! – Sifuentes

And absolutely shameful for Excel that you need an external tool to solve this basic requirement. – Radius

Answer 3

If you're dealing with a lot of cells to be copied, the selection.copy method will be extremely slow. (I experienced that when running a macro on 200 000 records).

A 100 times more performant way is to directly assign the value of one cell to another. Example from my code:

With errlogSheet
         'Copy all data from the current row

          reworkedErrorSheet.Range("A" & reworkedRow).Value = .Range("A" & currentRow).Value
          reworkedErrorSheet.Range("B" & reworkedRow).Value = .Range("B" & currentRow).Value
          reworkedErrorSheet.Range("C" & reworkedRow).Value = .Range("C" & currentRow).Value
          reworkedErrorSheet.Range("D" & reworkedRow).Value = .Range("D" & currentRow).Value
          reworkedErrorSheet.Range("E" & reworkedRow).Value = .Range("E" & currentRow).Value

Mannikin

Answer 4

Try this to copy whatever cell you have selected:

Sub CopyTheCell()
Dim TheText As String
TheText = Selection
ToClipboard TheText
End Sub

Function ToClipboard(Optional StoreText As String) As String
'PURPOSE: Read/Write to Clipboard
'Source: ExcelHero.com (Daniel Ferry)

Dim x As Variant

'Store as variant for 64-bit VBA support
  x = StoreText

'Create HTMLFile Object
  With CreateObject("htmlfile")
    With .parentWindow.clipboardData
      Select Case True
        Case Len(StoreText)
          'Write to the clipboard
            .setData "text", x
        Case Else
          'Read from the clipboard (no variable passed through)
            Clipboard = .GetData("text")
      End Select
    End With
  End With

End Function

Minuteman

Answer 5

This can be easily solved without bothering with VBA.

The user can paste the contents of the clipboard by Ctrl + Shift + V instead of more usual Ctrl + V (pasting as formatted).

Ctrl + Shift + V pastes the clipboard content as plain text.

Oporto

Excel successfully elevated this data handling to a new level. If you copy a cell that has a function in it, you can paste that as Value into an excel cell, or you can paste it into a text editor, as text. However if you edit a cell, then you CANNOT paste the text into it. That means if you have 2 cells, and you want to copy their values into a single cell, you need to first paste them into different cells as Text, the copy again from these cells, double-click on the desired cell and paste them into that, one by one. – Radius

Answer 6

In Excel 2013 you can do this with shortcuts.

Press Ctrl + Alt + V to open the paste special window. Now you can click the values radio button or just press V if your Excel is in English. If you don't use Excel in English you can see which button can be pressed to select the wanted option by looking at the underlining of the single letters.

Finaly press Enter to paste your copied selection.

Inconvenient

Answer 7

In Excel, highlight the cell in question. Hit F2. CTRL+Shift+Home. (This highlights the cell’s entire contents.) CTRL+C. Go to destination application. CTRL+V. It looks like a lot of steps, but when you actually do it, it’s much quicker than using the ribbons to accomplish the same.

If you need to copy multiple cells into an application bereft of the Paste Special… facility, then do a regular copy and paste from Excel into Notepad, and then do a copy and paste from Notepad to the destination. Cumbersome, but it works.

Patricio

The only reason we want to paste as text because the original "data" is NOT text. We don't have issues pasting text if the original data is text. If you press F2 on a cell that does not have text in it (but a reference or a function) then you will copy the reference/function text. – Radius

Answer 8

To accomplish this, I will copy the selected cells to clipboard, save the clipboard to a text variable, and then copy this text back to clipboard.

Copy the following into a new module and then run the last sub:

'Handle 64-bit and 32-bit Office
#If VBA7 Then
  Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
  Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
  Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, _
    ByVal dwBytes As LongPtr) As LongPtr
  Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
  Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
  Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
  Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
    ByVal lpString2 As Any) As LongPtr
  Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As LongPtr, _
    ByVal hMem As LongPtr) As LongPtr
#Else
  Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
  Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
  Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
    ByVal dwBytes As Long) As Long
  Private Declare Function CloseClipboard Lib "user32" () As Long
  Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
  Private Declare Function EmptyClipboard Lib "user32" () As Long
  Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
    ByVal lpString2 As Any) As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
    As Long, ByVal hMem As Long) As Long
#End If

Const GHND = &H42
Const CF_TEXT = 1
Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
'PURPOSE: API function to copy text to clipboard
'SOURCE: www.msdn.microsoft.com/en-us/library/office/ff192913.aspx
'Link: https://www.thespreadsheetguru.com/blog/2015/1/13/how-to-use-vba-code-to-copy-text-to-the-clipboard

#If VBA7 Then
  Dim hGlobalMemory As LongPtr, lpGlobalMemory As LongPtr
  Dim hClipMemory As LongPtr, x As LongPtr
#Else
  Dim hGlobalMemory As Long, lpGlobalMemory As Long
  Dim hClipMemory As Long, x As Long
#End If

'Allocate moveable global memory
  hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

'Lock the block to get a far pointer to this memory.
  lpGlobalMemory = GlobalLock(hGlobalMemory)

'Copy the string to this global memory.
  lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

'Unlock the memory.
  If GlobalUnlock(hGlobalMemory) <> 0 Then
    MsgBox "Could not unlock memory location. Copy aborted."
    GoTo OutOfHere2
  End If

'Open the Clipboard to copy data to.
  If OpenClipboard(0&) = 0 Then
    MsgBox "Could not open the Clipboard. Copy aborted."
    Exit Function
  End If

'Clear the Clipboard.
  x = EmptyClipboard()

'Copy the data to the Clipboard.
  hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:
  If CloseClipboard() = 0 Then
    MsgBox "Could not close Clipboard."
  End If

End Function

Function ClipBoard_GetData() As String
' Return the data in clipboard as text
' Source: https://learn.microsoft.com/en-us/office/vba/access/concepts/windows-api/retrieve-information-from-the-clipboard
#If VBA7 Then
   Dim lpGlobalMemory As LongPtr, hClipMemory As LongPtr
   Dim lpClipMemory As LongPtr
   Dim RetVal As LongPtr
#Else
   Dim lpGlobalMemory As Long, hClipMemory As Long
   Dim lpClipMemory As Long
   Dim RetVal As Long
#End If
   Dim MyString As String
 
   If OpenClipboard(0&) = 0 Then
      MsgBox "Cannot open Clipboard. Another app. may have it open"
      Exit Function
   End If
          
   ' Obtain the handle to the global memory
   ' block that is referencing the text.
   hClipMemory = GetClipboardData(CF_TEXT)
   If IsNull(hClipMemory) Then
      MsgBox "Could not allocate memory"
      GoTo OutOfHere
   End If
 
   ' Lock Clipboard memory so we can reference
   ' the actual data string.
   lpClipMemory = GlobalLock(hClipMemory)
 
   If Not IsNull(lpClipMemory) Then
      If lpClipMemory <> 0 Then
        MyString = Space$(MAXSIZE)
        RetVal = lstrcpy(MyString, lpClipMemory)
        RetVal = GlobalUnlock(hClipMemory)
         
        ' Peel off the null terminating character.
        MyString = Mid(MyString, 1, InStr(1, MyString, Chr$(0), 0) - 1)
      Else
        MsgBox "Clipboard is empty!"
      End If
   Else
      MsgBox "Could not lock memory to copy string from."
   End If

OutOfHere:

   RetVal = CloseClipboard()
   ClipBoard_GetData = MyString
End Function

Sub CopySelectedCellsAsText()
' Copy selected cells to clipboard, save the clipboard to a text variable,
'    and then copy this text back to clipboard
    If TypeName(Selection) <> "Range" Then Exit Sub
    Selection.Copy
    Dim strSelection As String
    strSelection = ClipBoard_GetData
    Application.CutCopyMode = False
    ClipBoard_SetData strSelection
End Sub

Anfractuous

Answer 9

This copied only text values from a date column, for me

Worksheets("Shee1").Cells(2, "A").Text

Achromatin

Answer 10

I know that this thread is a bit old, but I was looking for something similar and was recently playing with removing quotes from a copied range and that technique excludes other cell attributes as well, so it may work for you. First it copies the range as normal and then it manipulates the clipboard by taking text out and putting it back in. You could pass the range to make it more generic.

Sub CopyData()
'   Does a copy to clipboard without any formatting or double-quotes
'   Need to first create a reference in the VBE to Microsoft Forms 2.0 Lib
    Dim objData As New dataobject
    Dim strTemp As String
    Range("A1:A5").Copy
    With objData
        .GetFromClipboard
        strTemp = .GetText
        .Clear
        .SetText strTemp
        .PutInClipboard
    End With
End Sub

Nippon