Paste Special (values) not working all of a sudden?

[whispers]

Windowx XP
Office 2007

.xlsx file extension

(I have no browser open..as I have read random reports abotu Chrome, IE or FF being open and causing problems?)

I have a column (roughly 90k cells)

I format the column to be 12 numbers long (General >>> 000000000000)

works fine..

I copy this 'column'.. go to new column.. right click >> Paste Special >> Values..

and nothing.. copies only the BASE number in the cell/column..

not the padded number or value?


this has worked fine for years? now all of a sudden? (no windows or office updates lately either)


any advice?

I cant begin to explain how 'angry' I am right now.... a simple 'go-to' function is NOT working.. (especially when I need it!)

janky piece of software...

thanks

[Sam Capricci]

I found this one on this site...
=text(A2,"000000000000") and it worked for me formatting numbers into numbers w/leading zeros.
If that is what you want to do.

[Winon]

Hello whispers,

I copy this 'column'.. go to new column.. right click >> Paste Special >> Values..

Instead of Paste Special, use Paste and then Paste Special> Values.

Let me know if it works for you.

[whispers]

@Sambo kid -

thanks.

Idd really like it work the 'right' way and as it has all these years?




@Winon - no go..

I copied (say) 20 cells.. these were already 'formatted' to be 0000000000000 numbers

instead of going to NEW column and 'right clicking'.. I went to new column, first cell.. and hit the PASTE icon/button.. >> Paste Values

(didnt work).. it only copied the BASE number in the copied cell.. not the padded 0000.. number


What happened? what changed?

[Sam Capricci]

Well I'm trying on my iMac 2011 excel at home but I see what you mean.
But I did it and before pasting special I converted the cell to text. Might that be the problem? Then when I did that it worked.

[Winon]

Don't Paste Values first. Just paste, and then Paste Values while the range is highlighted,

[whispers]

nope.. doesnt work..

I copied cell(s)

pasted into new column
(while still highlighted)
chose Paste Special > Values

still only seems to be copying the FORMATTING.. not the VALUES

OG CELL: (after formatting custom > 000000000000)

000100109091


Copy OG cell..

go to new cell....

PASTE (either through right click or icon)

*while cell(s) still highlighted

PASTE VALUES or PASTE SPECIAL >> VALUES

(neither way works)


try this:


* type a number: 100764654

*select cell with number above in it.. and format it: (right click, format cell >> custom > 000000000000)

you should now get: 000100764654 displaying in the cell


copy cell.. go to new cell:

and either:
a.
PASTE ICON > PASTE
(while cell still selected)
PASTE ICON > PASTE VALUE

or

b.
RIGHT CLICK
PASTE SPECIAL
VALUES


doesnt work.. (but for YEARS it has?) same exact way.. no problems.. no all of a sudden? no more worky?



the cell will DISPLAY the correct number: 000100764654...

but if you select it and look above in the value bar... it is still only showing: 100764654... meaning it did NOT copy the VALUE.. but only copied the 'cell' with its formatting...

thanks

[Dave H]

Does your original data actually have all the leading zeros? It sounds like your data is just the base numbers then formatted in the custom format to display the leading zeros. How is the original cell formatted?

[whispers]

?

no.. as stated above.

it is a FORMATTED number.. (no leading zero's.. I apply that with formatting)

after formatting I am trying to copy/paste the value..

same as if it was an equation in there (SUM of 2 numbers).. and I want to only copy/paste the value/answer/sum...

[Winon]

Hello whispers,

Please could you upload a sample WorkBook of your problem? I find this very strange, and would like to see it for myself.

[Sam Capricci]

That is how I did mine and it worked. I put in a number in a cell 1230456, then went to format cell and custom and typed in 000000000000 then hit ok and it converted the cell to 000001230456 then I copied that cell and paste special into another cell and I got 1230456 but when I changed the destination cell to format - text (it was general) it put it in as 000001230456.

[whispers]

I changed the DESTINATION columns/cells to TEXT as well,.. both before & after pasting the 'values/data' into the column..

no dice..

here is the sample workbook:


(I only used a couple numbers instead of the 97k worth of rows I am working with)


thanks!

[Sam Capricci]

I see what you mean, I tried to copy / paste special values of both B2 then D2 into E2 and neither worked, it removed the leading zeros no matter how I formatted the destination cell.
It is funny that I opened your spreadsheet on the same platform as mine and my spreadsheet works but yours doesn't.
But my original =text formula did work, even though you noted you wanted the issue fixed.
Good Luck, I'll watch to see if somebody comes up with a solution.

[Winon]

Hello whispers,

Thank you.

I have Formatted columns B and D to your Custum Format down to Row 10000, and now it accepts the Copy>Paste Values.

Also see the "Please consider" note at the bottom of this post.

[Winon]

Hello whispers,

I forgot to mention that it is important to have the Range/s or Column/s Formatted to your requirements prior to doing a Copy & Paste Values.

[xladept]

I copied your formatted numbers, then chose another cell in the general area - right clicked, paste-special values and number formats - and no problem???

[Winon]

@ xladept,

Yeah, you are right, if you copy the formatted numbers it would work. But if you copied the unformatted number/s and tried to choose another cell in the general area - right click, paste-special values and number formats, it wont result to the required custum Format as what should be the desired outcome.

[xladept]

But Winon - that doesn't make any sense - how would it ever know what you meant??

[Winon]

@ xladept,

In a way you are right

that doesn't make any sense

, but what I was trying to point out was that if you copied an unformatted cell, and wanted to paste it to the desired Formatting, it will not work unless the Cell you are Pasting to, allready contains the desired format.

Hope that clarifies it a little, since it is almost 1:30am here, and LONG past my nap.LOL

Till later my friend.

@ whispers,

Glad I could help, and thank you for adding to my reputation.

[whispers]

@winon-

I added it before for replying and giving my thread attention!

I am at home now.. and can not check/test the file..

but I just want to be clear, that Im on the same page..



if I am taking a piece of data/value in a cell, that has the formatting applied to it..

why, no, all of a sudden, can I not copy/paste that VALUE (which should include the padding/leading 0's) into a new cell?

why would I have to pre-format the cell first? then past into it? (If Im reading your solution correctly?)

I should be able to select the final cell and look at the formula bar above and see all 12 number/characters.. (including the leading, padded zero's)


I dont want to 'copy' (use) an un-formatted cell.. I want to copy (use) a 'formatted' cell as my base/source.. and be able to paste the DISPLAYED value (all 12 characters) into another cell/column WITHOUT having any formatting applied to it.

it should should be the flat, 12 digit long number..

I'll try to check out your example file tomorrow at work..(I could be reading things wrong.. it late!)

thanks!

[Winon]

Hello whispers,

I was actually very tired towards my last post, and sort of lost concentration. xladept then also must have thought that there is something seriously wrong with me, or that I am just being plain stupid.

If you want to Copy a Formatted Cell with its value, you may just simply Copy and Paste, and the leading zeros will display in the new destination Cell. By default Excel always strips away any leading zero/s, from a Number or Text in the Formula Bar. Therefore it will never show any leading Zero/s with whatever Format of a Cell in the Formula Bar.

Also no need to pre-format any destination Cell/s, when you are Copying from an already Formatted Cell/s.

[whispers]

hmm.. ok so what Im reading is.. that there is no way to do it?

no way to get the actual 12 digit number to be the value/data in a cell from the formatted data/cell?

if I wanted to use the 'padded/leading' zero value in another equation.. say to CONCATENATE something.. it wont work.. because it ONLY grabs the base value (no-leading 0's)

=CONCATENATE(B1,"^STRING")
=CONCATENATE(B1,"^",C1")

none of it works.. here it a better illustration:

ORIGINAL FORMATTED STRING VALUE CONCATENATE RESULTS: DESRIED RESULTS
100768764 000100768764 STRING VALUE 1 100768764^100768764^STRING VALUE 1 100768764^000100768764^STRING VALUE 1
347629485 000347629485 STRING VALUE 2 347629485^347629485^STRING VALUE 2 347629485^000347629485^STRING VALUE 2


(attached)

[Winon]

Hello whispers,

If you want to revert to Formulae, to obtain the same result as a Customized Format, you may do so as illustrated in the attached Workbook.

[whispers]

well that wasnt what I was going for...

(wasnt trying to concatenate the leading 0's to the original value... I was trying to concatenate the formatted value (ie: w/ leadign 0's) to another colum or (hard coded) string value...

-but-

this has actually shown me a solution

if I 'concatenate' the original value with 'leading' 000's...

and then copy >> paste special >> values

I get the desired result I was looking for that started this whole thread..

(guess I just have to CONCATENATE 2 times)


thanks!

[xladept]

@ Winon

I was actually very tired towards my last post, and sort of lost concentration. xladept then also must have thought that there is something seriously wrong with me, or that I am just being plain stupid.

I had neither thought - I was confused and looking to you for direction

[Winon]

Hello whispers,

if I 'concatenate' the original value with 'leading' 000's...

and then copy >> paste special >> values

I get the desired result I was looking for

Yes! And to top it all, the leading zeros are not stripped off the base number in the Formula Bar displaying the contents of the newly "Pasted" Cell! What a bargain.

Thank you for the feedback. I enjoyed working with you on this "Monster".

Keep on Excelling, and stay in touch.

[Winon]

@ xladept,

Thank you for the kind words and understanding.

You do keep me on my toes with your sharp observations, and I do appreciate it.

Keep up the good work!