Copy/Paste

[elgringo56]

This is probably a little problem.

Cell A1 contains a Blank. Cell A5 contains a formula, =A1. I select cell A5. I then do a copy. I then select cell A7 and do a paste special, values only. Cell A7 ends up a 0 (zero) instead of a blank. This is causing me a real headache with a large field of blanks and data that I am trying to move and then sort. Blanks will sort at the end of a list, Zeros will sort at the beginning of a list. How can I make the paste operation enter a blank when the initial cell was a blank?

 

[Parra]

I had this problem this week and I think it was Jack from the UK that gave me the answer.

After you do the paste special value. Go to Data, Text to column and click on finish. This should help it did me, Let me know if it works.

 

[elgringo56]

Thanks, Para, but that just gave me a #REF error in the cell I copied to. This has me stumped

 

[Parra]

Maybe this will help...It was from Mark W.

...that "remaining something" is a text string ("") with a length of zero. Just because you can't see it doesn't mean nothing is there! If a cell contains "" that cell is nonblank (not empty).

You could select these cells, choose the Edit | Text to Columns... menu command, and press [ Finish ]. This will convert any cells containing "" into blank cells.

 

[elgringo56]

If you see this, this is what I did, Para. I found that in the cell with the =A1, if I changed that to =IF(A1="","",A1). This allowed nothings to be copy/pasted without zeros. I dont know why, but it did.