Free Microsoft Excel 2013 Quick Reference

Paste Special text or Unicode Text

i have two excel files, one that has formulas and one that uses raw values.
I need to copy the values from the first sheet to the second one and use
Paste Special and Values. However, sometimes the normal Paste Special box
does not appear, but another box that says Paste/Paste link with the options
of "text" or "Unicode text" appears.

Does anyone know what that happens???

Post your answer or comment

comments powered by Disqus

Since yesterday, I'm not able to paste properly anymore in Excel.
If I enter a formula in a cell, copy this and paste it in another cell (CTRL+V), it only pasts the value, not the formula itself.
If I copy the formula and click Paste Special, I'm only given the option to paste as Text or Unicode text.

If I copy the contents of a cell (CTRL + C) and try to paste it in multiple selected cells at once, using CTRL + V, I get the error "data on the clipboard is not same size and shape as the selected area, do you want to paste it anyway"
If I then say Yes, it only pastes the content in the 1st selected cell, the other selected cells it leaves empty.

What does work is copying a formula by pulling it down, using the cross at the bottom right of the cell.

I'm using Office 2007 on a Windows XP computer. I have already re-installed Office, but this hasn't helped. I have opened the same document on a different computer, without any difficulties.

Anyone any suggestion on how I can fix this?

Is it possible to set a keyboard short cut for 'Edit > Paste Special > Text"?

I am copying text from a website and pasting it into Excel and would like the formatting removed.



Its been sometime since I posted my queries in this forum. New IT
policy in the compnay, proxy net connection .. i dunno what, but Im not
able to access NG's through Outlook and posting through Google is....

I have some data in word which has to be transfered to Excel

Using Jon P's Excel to PPT automation examples I wrote the following
code in Word.

a) Problem is if I have some tables in Word then the table borders dont
get transfered to excel. Is this normal? On the other hand if I
manually copy paste from Word to Excel then do paste special and choose
HTML, one can get borders etc same as Word. if I use the syntax --

oExcelWorkS.Range("a1").PasteSpecial Format:=wdPasteHTML,
Link:=False, DisplayAsIcon:= _

then I get Compile error: named argument not found in Format above.

Why am I getting this error? What would be the correct synax?

b) I think my Present code is equivalent to a paste special - text or
unicode text (which one is it?) Whats the different between the 2?

c) In word Iam using early binding to excel and when I write the code
-- oExcelWorkS.Range("a1").PasteSpecial -- and immediately after
Pastespecial if I give a space then word prompts with some excel
constants - xlpasteall, xlpasteformats --etc, but when I use any of
these then I get a runtime error at this line (1004) -- paste special
method of range class failed? Why is this happening


Option Explicit

Sub TransferDatatoExcel()

'Set the reference to Microsoft Excel 11.0 Object library (or
10.0/9.0 _
depending on your version of Office) in Tools - References

Dim oExcel As Excel.Application
Dim oExcelWorkB As Excel.Workbook
Dim oExcelWorkS As Excel.Worksheet

Dim fileName As Double

Application.ScreenUpdating = False


Set oExcel = CreateObject("excel.application")
oExcel.Visible = msoTrue
Set oExcelWorkB = oExcel.Workbooks.Add
Set oExcelWorkS = oExcelWorkB.Worksheets("sheet1")

oExcelWorkS.Range("a1").PasteSpecial xlPasteValues

fileName = Now()

With oExcelWorkB
.SaveAs "d:Time Stamp - " & fileName & ".xls"
End With


Set oExcel = Nothing
Set oExcelWorkB = Nothing
Set oExcelWorkS = Nothing

Application.ScreenUpdating = True

End Sub

Currently I'm working on something where I have to copy data from a
website into Excel 2007, the data is usually a row (or a few columns of
the row) from a large table on the site. The problem I'm running into
(and have ran into when doing other things too) is that by default Excel
chooses to paste the selected text as HTML (I presume) which means that
all the data is pasted into one cell, while I want each column from the
table to go into a column in Excel. To accomplish this I need to select
Paste Special and then unformatted text (or Unicode text).

So I'm wondering, since I almost never want to paste as HTML, but rather
prefer pasting as unformatted text, if there is a way to change the
default pasting mode to unformatted text.

Erik Wikström

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.

"Pinky, are you pondering what I'm pondering?"
"I think so, Brain. But what if the Earl of Essex doesn't like burlap

Excel 2003

In Internet Explorer, I view the source code of my Web page in Notepad,
then copy that text and paste it into an Excel worksheet.

Excel gives me only two choices in Paste Special (text and unicode),
both of which causes a problem. They both paste as interpreted HTML
code and so my worksheet tries to render as a Web page. I don't want

So after pasting and after the rendering, I click the paste option
button that appears, and choose "Use text import wizard". In the wizard
I choose "Fixed Width" and click Finish. The HTML code now appears in
my worksheet correctly as HTML code (text).

I want this clipboard-pasting routine in VBA, but can't figure it out.
It won't record the paste option button, so that's no help.

How do I paste using VBA as described above or using another paste
method where I get all the source code text on my clipboard into a
worksheet as text?

Many thanks,

When pasting text that contains back slashes, text is split into multiple
cells using the back slash as a deliminator, this occurs automatically and I
wish to stop it.
Pasting as text or unicode text via paste special makes no difference.
Example text 'C:Program FilesCommon FilesMicrosoft Shared' splits into 4
cells in a row and the back slashs removed
Using Excel 2003 SP1

This is happening in both 2007 and 2010. I am trying to record a simple macro that involves the NOW() function. But after the NOW() executes I need to replace the cell's contents simply with the values the program produces. Easy right? Just go to "paste special" and choose "Values." Nope. Tried it in Excel 2007 and 2010, and instead I get this weird menu that I am not familiar with that gives me an option of pasting in text or uniform or unicode text or something. I am working with a textbook and this is happening in direct contradiction to what the textbook says should be happening. Is there some setting I am missing? Why won't my normal paste special menu come up??? Any master out there that can help me would have my sincerest thanks.

When I try to use paste special to copy a format or a formula the box that usually gives you the : formula, Values formats etc etc is no longer there I just get a box giving the option for text or unicode text - how can I get this to alter back so I ahve the other option - HELP

When pasting a numbered list of interview responses from word to excel using
the paste special - text option I get one column with the number and one
column with the text, which is what I need. However, in some cells, the text
is cut short leaving me with responses that end in mid sentence. I'm using
office 2003 products.

Hi All,

I'm trying to stop users from using paste and overwriting cell validation
and formatting. However, I can't disable the paste function due to
requirements. I'm trying to find out if, and how, I can capture the paste
event so I can replace it with paste special in VBA.

Also, the users could be pasting from a number of different programs. How
do I identify if Paste Special/Values or PasteSpecial/Text is needed for each
instance, or do I not have to differentiate.

Thanks in advance for you assistance,


I have an Excel sheet I am building to help calculate some stuff for an automated script that we use often

This is how I want it to work -

Column A

Column B

Column C
"Concatenate" (this is a list of characters to format the list from Column A correctly with spacing and commas and quotes)

Column D
"Concatenate" (this is a list of characters to format the list from Column B correctly with spacing and commas and quotes)

Column E
I want to have auto pasted the concatenated text from Column C show it is just showing the value (Paste Special - Values) instead of the formulas.

Column F
I want to have auto pasted the concatenated text from Column D show it is just showing the value (Paste Special - Values) instead of the formulas.

I cannot figure out how to tell Excel to paste these concatenated values so it is just showing the text values of the formula instead of the formula itself.

Is there a way to do this?

Is there a way to only allow paste special values or formulas within a cell? People are coping and pasting and screwing up the formating

So I've got 11 workbooks with thousands of entries and I'm trying to combine
them all into one Master Workbook. Initially, I went to each sheet,
highlighted, copied, and then went into my master pressed paste special and a
menu like this: (I need to choose text
because I have a bunch of macros behind my text and I can't let it be
formatted) Let's call it menu 1.
I did a whole book with that menu 1, now when I paste special a menu like
this: Menu2 pops up, and it doesn't have the
option to paste special text like I need. I found out that if you copy
outside of Excel that it will go straight to your windows clipboard (which
leads me to menu 1) but I need to copy from Excel and it only takes me to
office clipboard and won't work.

I have been copying some data from the web using Copy Then Paste special, Text. Then I go back in a use Text to Columns to seperate the string of data. Now for no apparent reason, at least to me, when I Paste special, Text the data gets pasted as if I were using Text to Columns. If I copy a name such as John Jones, I get John in one cell and Jones in another and I have to go back in and edit the data. I didn't change any settings but something happened. Any ideas?

Jim O


Is there a way in excel to block the paste functionality and allow only
paste special - values. Or even better automatically only paste values
and NOT formating etc when paste is used?

Many thanks in advance


Hi, I have this sheet this morning that has lots of numbers, alinged in center with error beside them that says "Number Stored as Text". At first I just tried format the whole column as a number, but that changes nothing. Can someone explain why that does not change anything. I thought it would get rid of all the Error codes but it does nothing. I don't want to turn off the Error code as I need to see what numbers were formatted like this.

I am guessing someone cut and pasted these numbers in, and maybe pasted as TEXT or Unicode Text, right now I am clicking on small groups of them and converting them to a number.

I also tried high lighting the whole SS and removing all formats (that removes everything except this TEXT tthing), I need to reformat and rebuild this whole sheet anyway. But when I do that it still does not convert all these numbers that are formated as text back to numbers. I have a 1000 lines or so is there a faster way to convert this, if I paste this whole sheet into a new sheet and use Paste Special-values would that then paste the TEXT back in as numbers?


Just recently I was able to copy material from a website and paste into excel and not lose any formating (colour, font..etc) but now all of a sudden all I can do is paste unformatted text or unicode text. Is there any reason for this?



I recently had a problem with Excel on my work computer where it didnt want to paste special. It would only paste the values and when ever I tried to use "paste special", I got a message about "unicode text" or "text".

After hours of trying to figure it out, I called the IT guy and he figured it out.

It was the SKYPE to CALL function that I had installed on my machine when I was installing skype the week before.

He un-installed that function by going to the control panel and removing the skype to call funtion but he did not remove all of skype b/c he said the auto calling function was the issue NOT the whole skype program.

After restarting the machine, excel was restored to its original functionality.

I just thought this might be helpful to somebody somewhere.


Stephen K

When I copy from Excel 2003 (values & formulas) and paste special into Excel 2007 i get the option screen to select unicode text,sylk etc instead of the other screen with the option of values,formulas,formats etc.How can I select the option for value,formulas?
Sorry cannot attach a screen shot as it is above the allowed limit.

Hi, how can i do in one macro paste special function but from 2 interfaces.
I want to paste special - value to excel sheet by button. I have this 2 types of macro, but i don know how can i get it together.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
i copy any value and i want after clicking on button paste special this value. But it can be from other excel sheet or from our system. Can i combinate this 2 rows or can i write it in one line with condition (if doesnt work first, use second?

now it works only if i have in macro only one possibility - the first or second. But if is there only the one line, copying from the other window gives me error. If i give both lines it gives me error too.
pls. can you help me?

ps: can i remember history after run macro? After all macros what i have i can not make undo, because it clear history of undo.


When I try to use Paste Special, the normal menu of choices (value, formula,
etc.) does not appear. Instead, a dialog box asking if I want to paste as
Unicode Text or Text appears. Also, when I right-click a cell and choose
'copy' then move to another cell and right-click to 'paste' the 'paste' and
'paste special' are grayed out - I have to repeat the operation for the
choices to become active. Is there a setting that has changed? It used to
work fine, but now for some reason Paste Special is not special anymore.

I used to be able to right-click on a cell, copy then right-click on another
cell and choose from a dialog box either 'all', 'formulas', 'value',
'formats', etc., but now all that comes up is a dialog box asking if I want
to paste as 'Unicode Text' or 'Text'. How do I get back the original Paste
Special dialog box?

I have a small program that drops text from a Word table into Excel
using Paste Special as Text.

Although Paste Special as Text should insert the contents of the
clipboard as text without any formatting this does not appear to be
happening as it did with Excel 2000 .

Excel 2003 splits the data when it meets a New Line or Carriage Return
and drops anything after it down to row below.

Any ideas what is causing this behaviour
Thanks for your assistance

No luck finding an answer? You could always try Google.