How to paste text without formatting almost anywhere

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!

9 Answers 9

Something like this?

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

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

How to paste text without formatting almost anywhere

On Windows 10, when using the Microsoft Word app, pasting content from a web browser, another document, or any other program will include the text along with the source formatting, whether you use the “Paste” option from the “Home” tab or the “Ctrl + V” keyboard shortcut.

Although you can always right-click to open the context menu and select the “Keep Text Only” option to perform a clean paste without formatting, these are additional steps you probably won’t remember every time you copy content from the web or another app.

Fortunately, just like in OneNote, if you’re using an Office 365 subscription or the standalone version of Office, Word 2019, 2016, and older versions include settings to set the default paste behavior to keep only the text without any weird formatting.

In this Windows 10 guide, we walk you through the steps to change the default paste settings of Microsoft Word to paste text from other programs without formatting.

How to set default paste settings for Microsoft Word

To paste text directly into a Word document without source formatting, use these steps:

    Open Microsoft Word.

Create a blank document.

Quick tip: You can save the extra steps to create a blank document using this tip to skip the start screen in Office.

Click on Options.

Under the “Cut, copy, and paste” section, use the “Paste from other programs” drop-down menu and select the Keep Text Only option.

How to paste text without formatting almost anywhere

(Optional) Use the “Pasting between documents” drop-down menu, and select the Keep Only Text Option.

Quick tip: We’re only setting a new default for pasting content from the web, between documents, and other programs, but Microsoft Word also allows you to keep only the text as the default option when pasting within the same file as well as when there’s a styling conflict between documents.

  • Click the OK button.
  • Once you complete the steps, when copying fragments from the web, between documents, or another app, you can use the “Ctrl + V” keyboard shortcut or the “Paste” button from the “Home” tab to paste only the text without the source formatting.

    If you need to keep or merge the formatting of the content, you can still open the context menu in Word and select the pasting option.

    More Windows 10 resources

    For more helpful articles, coverage, and answers to common questions about Windows 10, visit the following resources:

    Portable (and affordable) power accessories we love

    Each and every one of these charging gadgets will keep your favorite gear and gadgets going for longer, and none of them costs more than $30.

    How to paste text without formatting almost anywhere

    VisionTek 8,000 mAh micro-USB power bank ($13 at Dell)

    This compact dual-output powerbank can speedily recharge any and all your devices, thanks to a two-amp “fast charge feature,” using its micro-USB out port. Its simple design includes an LED indicator, and it costs about as much as a single ticket to the movies.

    How to paste text without formatting almost anywhere

    Panasonic eneloop AA batteries (From $13 at Dell)

    Panasonic’s rechargeable batteries are among the best available, and just a couple of them will keep your favorite remote, mice or other peripherals powered up when you need them. They’re also eco. And the company’s affordable charger fits and charges both AA and AAA batteries at the same time.

    How to paste text without formatting almost anywhere

    Belkin Qi Wireless Charging Pad ($30 at Dell)

    This unobtrusive Qi wireless charging pad looks good (and kind of like a UFO …) and easily charges all your Qi-compatible device up to 5W. Its LED indicator lights up when you’re charging. And it costs just $30.

    We may earn a commission for purchases using our links. Learn more.

    How to paste text without formatting almost anywhere

    The semiconductor crisis is worsening and industries are clashing

    Countries and companies are scrambling to boost chip output and combat the ever-worsening global semiconductor shortage. However, solutions aren’t materializing fast enough to avoid having entire industries at each other’s throats.

    How to paste text without formatting almost anywhere

    Halo Infinite: These are the weapons you can use during the beta

    There are plenty of tools of destruction that players have at their disposal during the Halo Infinite beta testing. Here’s a complete list of every weapon you can pick up and use.

    How to paste text without formatting almost anywhere

    Intel’s two new Arizona chip factories have begun construction

    It’s been in the works for a while, and it’s finally time for steel to be applied: Construction has begun on Intel’s $20 billion investment in new Arizona-based chip factories.

    How to paste text without formatting almost anywhere

    The NFL is back! Check out these must-have Windows apps for football fans

    After months of waiting through the offseason, the NFL is finally back this week. With these Windows 10 apps, you won’t miss a snap of the NFL action.