 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2007Paste Special, Text in Excel 2007
I'm copying data from a web site to an Excel 2007 worksheet. Specifically, I'm creating a database of my DVDs and using IMDB as a source of information, specifically the release date of the movie. Right now I'm copying the date from the web page, right-clicking on the appropriate cell and selecting Paste Special and then selecting Text. This is getting to be too repetitive to be convenient. Is there a keyboard shortcut to do this? I'd love to just hit Ctrl-V (or something similar) to paste it as plain text with no HTML codes or formatting.  Signature"Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" Hi Vernon I assume you mean Paste Special>Values The following code will do this for you Sub PasteSpecialValues() Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub To copy this code to your workbook, press Alt+F11 to invoke the VB Editor. Insert>Module In the white pane that appears, copy the code as above Alt+F11 to return to your Excel sheet Press Alt+F8 to bring up Macros and with the above macro highlighted choose Options and set whatever letter you wish to use in addition to Control to invoke the macro. You could use Shift+v, then Control+v would be paste, but Control+V would be your Paste Special. SignatureRegards Roger Govier > I'm copying data from a web site to an Excel 2007 worksheet. Specifically, > I'm creating a database of my DVDs and using IMDB as a source of [quoted text clipped - 4 lines] > I'd love to just hit Ctrl-V (or something similar) to paste it as plain > text with no HTML codes or formatting. You can also add the PasteSpecial values button to the QAT to do it with one click Click on the arrow on the Paste button on the Home tab and then right click on "Paste Values" and choose add to QAT SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > I'm copying data from a web site to an Excel 2007 worksheet. > Specifically, I'm creating a database of my DVDs and using IMDB as a [quoted text clipped - 4 lines] > keyboard shortcut to do this? I'd love to just hit Ctrl-V (or something > similar) to paste it as plain text with no HTML codes or formatting. Hi Ron I hadn't realised paste Values was available as a single option to add to the QAT until I looked at All Commands. I could see the Paste Special in Popular commands, but that would still require a selection of values. Thanks for bringing this to my attention. SignatureRegards Roger Govier > You can also add the PasteSpecial values button to the QAT to do it with > one click [quoted text clipped - 11 lines] >> keyboard shortcut to do this? I'd love to just hit Ctrl-V (or something >> similar) to paste it as plain text with no HTML codes or formatting. Hi Roger In Excel versions < 2007 there is also a button to do this that you can add to a toolbar Tools>Customize Click on Edit on the Commands tab Drag and drop a button with 12 on it on one of your toolbars SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > Hi Ron > [quoted text clipped - 19 lines] >>> keyboard shortcut to do this? I'd love to just hit Ctrl-V (or something >>> similar) to paste it as plain text with no HTML codes or formatting. Hi Ron It's amazing how blind one can be for 10 years!!!! Many thanks SignatureRegards Roger Govier > Hi Roger > [quoted text clipped - 29 lines] >>>> something similar) to paste it as plain text with no HTML codes or >>>> formatting. Hi Roger > It's amazing how blind one can be for 10 years!!!! That's the beauty of Excel. You will never know everything SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > Hi Ron > [quoted text clipped - 34 lines] >>>>> something similar) to paste it as plain text with no HTML codes or >>>>> formatting. On 11/4/2007 3:09 AM, Ron de Bruin went clickity clack on the keyboard and produced this interesting bit of text: > You can also add the PasteSpecial values button to the QAT to do it with > one click > > Click on the arrow on the Paste button on the Home tab and then right click > on "Paste Values" and choose add to QAT "Paste Values" is grayed out when I do what you suggest. Here's what's happening and what I want: I go to IMDB, select the text for the release date of the movie and copy it to the clipboard. I go to Excel, right-click on the cell in which I want to paste the data and select Paste Special. Up pops a window that says that has a "Paste" radio button selected (all other options are grayed out) and a pane in which are three options: HTML, Unicode Text and Text. I should note that the cell in which I'm pasting this in is formatted as date, not general. SignatureNo dice, Chicago. I'm giving the orders and we're coming in. I guess the foot's on the other hand now, isn't it Kramer? Hi Vernon >> "Paste Values" is grayed out If the clipboard is empty it is also grayed out when you copy/paste in Excel If you copy from a webpage use the text option in the dialog you get. The PasteSpecial values option is not working then SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > On 11/4/2007 3:09 AM, Ron de Bruin went clickity clack on the keyboard > and produced this interesting bit of text: [quoted text clipped - 14 lines] > and Text. I should note that the cell in which I'm pasting this in is > formatted as date, not general. On 11/4/2007 8:54 AM, Ron de Bruin went clickity clack on the keyboard and produced this interesting bit of text: > Hi Vernon >>> "Paste Values" is grayed out [quoted text clipped - 3 lines] > If you copy from a webpage use the text option in the dialog you get. > The PasteSpecial values option is not working then But the question is, is there a way to just make this the default way of pasting text, rather than having to go through the context menu? SignatureA belly button is for salt when you eat celery in bed. See Roger's answer that use a macro with the shorcut Ctrl v You can add the macro in your personal.xlsb See http://www.rondebruin.nl/personal.htm SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > On 11/4/2007 8:54 AM, Ron de Bruin went clickity clack on the keyboard > and produced this interesting bit of text: [quoted text clipped - 8 lines] > But the question is, is there a way to just make this the default way of > pasting text, rather than having to go through the context menu? On 11/4/2007 9:17 AM, Ron de Bruin went clickity clack on the keyboard and produced this interesting bit of text: > See Roger's answer that use a macro with the shorcut Ctrl v > You can add the macro in your personal.xlsb > > See > http://www.rondebruin.nl/personal.htm Thanks to both of you! SignatureA cat assures its owner of good luck. - Chinese Proverb I add a new page on my site today that explain how you can change the image of the button. http://www.rondebruin.nl/imageqat.htm SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > You can also add the PasteSpecial values button to the QAT to do it with one click > [quoted text clipped - 9 lines] >> keyboard shortcut to do this? I'd love to just hit Ctrl-V (or something >> similar) to paste it as plain text with no HTML codes or formatting. Great - something else I have to put into my Favorites list! For the record: your workbook with all the icons for Command Bar Face IDs has just been a goldmine for me. > I add a new page on my site today that explain how you can change the image of the button. > http://www.rondebruin.nl/imageqat.htm [quoted text clipped - 12 lines] > >> keyboard shortcut to do this? I'd love to just hit Ctrl-V (or something > >> similar) to paste it as plain text with no HTML codes or formatting. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.