Cells keeping a "memory"

Hi guys,

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).

Originally Posted by Special-K

#VALUE isn't an indication an empty cell
Using LEN() and it returning 0 is what properly identifies an empty cell.

Use LEN() CODE() to identify what you think are empty cells and ascertain what value they really are

Thanks special K I already tried len function and nothing is there.

Originally Posted by Aardigspook

Unfortunately, a 'blank' cell created by using "" isn't the same as a truly empty cell. If you use ISBLANK(cellref), you'll see that even the paste-value of the TRIM() formula gives a result of FALSE for ISBLANK*. That's because Excel treats the pasted value as a Constant. Try selecting your area then check what Excel thinks is in various types of cells - press F5 (to open 'Go to') then click 'Special' and see which cells are selected when you choose 'Constants' or 'Blanks' or 'Formulas'.

Unfortunately, I don't know any non-VBA way around this apart from manually deleting the apparently-blank cells. I'm sure it's possible in VBA, but I'm not a VBA guru so don't know how. You could post a question in the 'Excel Programming / VBA / Macros' forum if you'd like to try a VBA solution.

* ISBLANK should really be called ISEMPTY, I think.


Sorry I can't be of more help.

Thank you Aardigspook!

Thank you guys for the feedback and brainstorming.

What worked well for me until I get a better solution was to:

1. Paste the data from the web page in column B
2. Generate an increasing serial # in column A
3. Sort column B with "Sort A to Z" to get all those "empty" cells at the top
4. Manually select those rows and deleting them
5. Sort column B with "Sort A to Z" to recover the original order of my data

Not so fancy but does the trick!
Thanks again and have a nice one!

anyway you were completely right, I found a VBA coding that does the job in like .01 msec...
https://www.thespreadsheetguru.com/t...-with-vba-code