Paste only value, not the style

excelnow

Board Regular
Joined
Nov 17, 2009
Messages
106
Hi,

I frequently copy numbers from a web page and paste them into my Excel file. When I paste the number into a cell, Excel preserves its font family, font size and color. I want to paste only the value of that number, without the style. Is this possible (without having to double click the cell or right click and select special > text every time I will paste)?

I'm using Excel 2007.

Thanks for any ideas.
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
The only other shortcut I can suggest is for Windows only, using a keyboard with a "Menu" key (usually somewhere to the right of the spacebar):

This is not a keystroke chord, it's an arpeggio: keys are depressed and then your finger is lifted after each keystroke.
Press menu key, then press M, then press Enter.
 
Last edited:
Upvote 0
The only other shortcut I can suggest is for Windows only, using a keyboard with a "Menu" key (usually somewhere to the right of the spacebar):

This is not a keystroke chord, it's an arpeggio: keys are depressed and then your finger is lifted after each keystroke.
Press menu key, then press M, then press Enter.

Thank you, but 1) it didn't work for me in Excel 2007, 2) Double-clicking the cell is really simpler than pressing three keys on the keyboard consecutively. I am looking for an option, or VBA code to handle it automatically.
 
Last edited:
Upvote 0
If three keystrokes is too much for you then you are going to limit yourself.

One option is to paste then clear the formats, which is ctrl+v (paste) then alt -> h -> e -> f (clear formats) = 6 keystrokes

The macro option will also involve a bit of work, if you don't want to lose an establish shortcut you'll need to include the pressing of shift to run the macro from the keyboard, ctrl + shift + {key} = 3 keystrokes. But you also have the initial setup to consider...
 
Upvote 0
I would think something like this would work.

Its a sheet change event script.

When you paste in your text.

The new formatting will be done for you.

You will see my sample.

Modify the script to your liking.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
       
    End With
    
    Selection.Font.Bold = False
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub
 
Upvote 0
Thanks for the sample code. It does what I need but it also removes the format for the number of decimal places I have for that cell. Besides, it prevents the use of Undo (Ctrl + Z) after a paste. Wish Excel had a simple built-in option for such a simple necessity.
 
Upvote 0
There is no undo function when using Vba. Or if there is one I do not know of one.

Try this:

Look at the part in red and change to your liking:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
       
    End With
    Selection.NumberFormat =[COLOR=#ff0000] "0.00"[/COLOR]
    Selection.Font.Bold = False
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub
 
Upvote 0
This is going to largely depend on how the page you are copying in is set up. Seeing as the macro setup option was in your sights why not just import the web page to Excel?

Data >> From Web >> Enter the URL > choose what you want to import >> OK

This will save you having to switch applications for your data, just refresh as and when needed and copy out the data you need.
 
Upvote 0
Thanks @My Answer Is This.

I guess I can live without Undo after the paste. I decided to use this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .ColorIndex = xlAutomatic
        .Bold = False
    End With
    Selection.NumberFormat = "0.00"
End Sub
 
Last edited:
Upvote 0
Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.
Thanks @My Answer Is This.

I guess I can live without Undo after the paste. I decided to use this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .ColorIndex = xlAutomatic
        .Bold = False
    End With
    Selection.NumberFormat = "0.00"
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top