Copy/Paste

elgringo56 · Apr 26th, 2002, 05:05 PM

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 · Apr 26th, 2002, 05:13 PM

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 · Apr 26th, 2002, 05:25 PM

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

Parra · Apr 26th, 2002, 05:44 PM

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 · Apr 26th, 2002, 07:15 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 05:05 PM	#1
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	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?

Apr 26th, 2002, 05:13 PM	#2
Parra Board Regular Join Date: Feb 2002 Location: Los Angeles, CA Posts: 752	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.

Apr 26th, 2002, 05:25 PM	#3
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	Thanks, Para, but that just gave me a #REF error in the cell I copied to. This has me stumped

Apr 26th, 2002, 05:44 PM	#4
Parra Board Regular Join Date: Feb 2002 Location: Los Angeles, CA Posts: 752	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.

Apr 26th, 2002, 07:15 PM	#5
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	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.