I´ve got a issue that is driving me crazy.
I am pasting data from a web page into an excel workbook. I paste the whole information in a single column A, some of the rows seem to be blank but no! plenty of non printable characters. I need them to be blank so in column B I create a formula with nested trim, clean, substitute (char(160, etc, -lot's of them) referencing to A1 and I extend this formula. Then copy column B and paste it as value in column C. Now I think that those empty looking lines should be really empty, actually if I create a formula with =code() with reference to one of those the result is #VALUE! which is the same result i get with a really empty cell.
But then, if I go to cell C1 (the one where I pasted the data after trimming, cleaning, subtituting special characters) and do Ctrl + ↓key (arrow going down key) the active cell go straight to the last cell of the list (not stopping in "blank cells" as it should). If i position over one of those "blank" cells and press the delete key, then the Ctrl + ↓ stops at the deleted key.
You can easily recreate this with the formula =trim() referencing to a blank cell. The result should be nothing right? but if you copy that cell and paste value in another cell and do the Ctrl + ↓ stuff I stops at this cell too!!
My questions is: does excel cells keep a memory when you copy/paste value that the origin of the copy/paste was not blank even if the result was blank?? (eg. trim of a blank cell has a blank result but the cell actually has a formula).