Macros to “Paste as Text” for Word and Excel

These two macros will save you a lot of time.  You will used them every time when you need to copy something from external source (like Internet page) into a Word or Excel document, but  you do not need all the colors and formatting.   You just need the text.

Normally, you would do this with Paste Special, but this is several awkward clicks and many seconds lost.  Over the course of the day this is a huge inefficiency.

 

Word

Let’s write a macro for Word first. As of 2012-01-11 WED, I adopted this little macro as final version:

Sub PasteText()
    On Error GoTo Unicode
    Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
        Placement:=wdInLine, DisplayAsIcon:=False
    GoTo Done
Unicode:
    Selection.PasteAndFormat (wdFormatPlainText)
Done:
End Sub

This macro was tested with Word 2007, and it can handle paste from Web and from Visuals Studio.   First statement (Selection.PasteSpecial) handles rich text formats, and second statement (Selection.PasteAndFormat) deals with pasting Unicode strings.

This is a very simple macro.  It works in all Word versions, including  Office 2010.  It works multiple times, so you can paste the same string over and over again.  Who can suggest a better way of doing this without using dreaded “GoTo” statements?

Regular paste is Ctrl+V.  I recommend to map this macro to Alt+V key combination.

To map your new macro to a keyboard combination in Word 2007 do these simple 10 (Ten!) steps:

01 Click Office Button
02 Click Word Options button
03 Click Customize button
04 Click Customize button
05 Pick Macros in Categories drop-down list
06 Select your new macro in Macros drop down list
07 Position your cursor into Press new shortcut key
08 Press Alt+V
09 Click on Assign button
10 Press Close and Cancel

 

Excel

Now let’s proceed to Excel.  In Excel open visual basic (Alt+F11) and paste this code into one of the PERSONAL.XLS modules:

Sub PasteValues()

    On Error GoTo ErrorHandler

 If Application.ClipboardFormats(1) <> -1 Then

    If Application.CutCopyMode Then
        'Paste values, if in CutCopyMode
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    Else
        'Paste text, if from external source
        ActiveSheet.PasteSpecial Format:="Text"
    End If

 End If

    GoTo FinishMacro

ErrorHandler:
    'Error will be caught, if there is nothing to paste
    Resume Next
FinishMacro:

End Sub

As you can see in Excel, it is a little more work.  The problem here is that that there are two possible scenarios.  One is Copy and Paste Special inside Excel, and the other is Copy and Paste Text from outside the Excel.  And this macro should handle both scenarios.

 

Place your MACRO inside PERSONAL

We are suggesting to use Shift+Ctrl+V combination for this macro.  To assigns a keyboard shortcut  to a macro in Excel, use this four steps:

01 Press Alt+F8 to open Macros dialog box
02 Press Options button
03 Press Shift+V and
04 Press OK and Cancel

Moment of truth. Now you can start shaving precious seconds, when you copy formatted text from outside sources into your Word or Excel documents.

2016-02-13 SAT – Updated after series of comments from  Chuck =======>:

Of course, you need to place your macro inside PERSONAL.XLSB file for all instances of Excel to have access to this functionality.

 

(Visited 99 times, 1 visits today)

7 Comments

  1. Chuck, try this:

    Sub PasteValues()

    On Error GoTo ErrorHandler

    If Application.ClipboardFormats(1) -1 Then

    If Application.CutCopyMode Then
    ‘Paste values, if in CutCopyMode
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Else
    ‘Paste text, if from external source
    ActiveSheet.PasteSpecial Format:=”Texto unicode”, Link:=False, _
    DisplayAsIcon:=False

    End If

    End If

    GoTo FinishMacro

    ErrorHandler:
    ‘Error will be caught, if there is nothing to paste
    Resume Next
    FinishMacro:

    End Sub

  2. Thank you so much! This is what I needed to copy from external source. Majority of forums I went to search for answers show solutions for copying from excel workbook only.

  3. Yes. Perfect. I also share PERSONAL.XLS across multiple computers over the network. Still, I always place macros that apply to entire Excel application into PERSONAL.XLS for all user to have access to. If several user open Excel at the same time, they simply receive a warning, and they have to open the file in a Read-Only mode.

  4. Thanks so much for your response, Udar.
    I’m using Excel 2013 for a spreadsheet in a shared network drive (so I have placed the macro into a standard module rather than a PERSONAL.XLS module).
    I needed to disable all paste-with-formatting options as, despite being asked not to, other users have forgotten and out of habit have pasted in his way, which causes havoc with my conditional formatting and protected cells.
    So, I have assigned the macro to ctrl+v, to overwrite the standard paste for that command.
    Users will be pasting from the internet, Word documents and other Excel spreadsheets, and whilst the macro works brilliantly for the first two (and for copy-pasting within the same worksheet) it will not work across spreadsheets or across workbooks. It’s not that it goes back to pasting with formatting in theses instances – it just doesn’t paste at all.
    I’m pretty new to Excel so I’ll admit to having not much clue about what the problem could be!
    Any help would be much appreciated – I’ve tried other macros I’ve found on the net but yours has come the closest to doing what I need!
    Thanks

    Chuck

  5. Hi Chuck,

    Thank you for your question. I cannot reproduce the behavior that you are describing. I am using latest Office 365 and Excel 2016. I just tested several scenarios:
    – I can paste as plain text into other instance of Excel (with my custom Shift+Ctrl+V),
    – I can paste into other applications (using normal Ctrl+V)
    – I can even paste into applications at a remote desktop session (using normal Ctrl+V).

    Here is the code one more time:

    Sub PasteValues()
    ' Keyboard Shortcut: Ctrl+Shift+V
    ' 2005/12/18 Sun
    ' 2009-10-30 FR Check Application.CutCopyMode

    On Error GoTo ErrorHandler

    ' 2011-05-05 TH Check, if Clipboard is empty
    If Application.ClipboardFormats(1) <> -1 Then

    If Application.CutCopyMode Then
    'Paste values, if in CutCopyMode
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Else
    'Paste text, if from external source
    ActiveSheet.PasteSpecial Format:="Text"
    End If

    End If

    GoTo FinishMacro

    ErrorHandler:
    'Error will be caught, if there is nothing to paste
    Resume Next
    FinishMacro:

    End Sub

    Please describe you situation in more details.

  6. Hi,

    I used the code for Excel and it does pretty much what I need – except for pasting from anywhere in Excel other than the worksheet copied from, i.e. I can copy and paste within a worksheet, but not from another worksheet or another Excel workbook. It just pastes nothing when I try. Is there any fix for this at all please?
    Thanks

  7. I’m not familiar with VBA syntax and I’ve found your macro very useful. THANK YOU !

    I’ve also found another way to do it but I’m not sure if that option pastes in UNICODE format. If it works is as quick as yours. It consists of setting the default paste options to your preferences in Word Options. The steps would be these (I have Word 2007 in Spanish, i.e. the original names in English may differ from these):

    01 Click Office Button
    02 Click Word Options button
    03 Click Advanced
    04 Go to Cut, Copy & Paste Options
    05 Select “Keep text only” in the different options to paste between documents or to paste from another applications
    06 Press Accept

    Perhaps you could confirm it. I don’t know how to check the type of pasting (Unicode or plain text)
    THANKS AGAIN !!
    Manuel

Your question, correction or clarification Ваш вопрос, поправка или уточнение

Editor on duty will review your comment. Can't wait? Discuss your topic right now in forums, if you register and login.     Required fields are marked with * Ваше послание пойдет на просмотр в редакцию. Не можете ждать? Обсудите ваш вопрос прямо сейчас в форуме Русский Круг , ecли вы зарегистрируетесь и войдёте под своим именем.     Поля, обязательные для заполнения, помечены *
* *