+ Reply to Thread
Results 1 to 11 of 11

Cells keeping a "memory"

  1. #1
    Registered User
    Join Date
    04-27-2014
    Location
    Argentina
    MS-Off Ver
    Excel 2010
    Posts
    12

    Cells keeping a "memory"

    Hi guys,

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

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Cells keeping a "memory"

    #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
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Cells keeping a "memory"

    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.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Registered User
    Join Date
    04-27-2014
    Location
    Argentina
    MS-Off Ver
    Excel 2010
    Posts
    12
    Quote Originally Posted by Special-K View Post
    #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.

  5. #5
    Registered User
    Join Date
    04-27-2014
    Location
    Argentina
    MS-Off Ver
    Excel 2010
    Posts
    12
    Quote Originally Posted by Aardigspook View Post
    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!

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Cells keeping a "memory"

    I suggest that you copy from the web and paste into Word. In the Paragraph Group, select the Show/Hide formatting button. This will show you what is in the selection that you copied from the web. Clean up the text in Word then copy and paste into Excel as Word has far better text editing capabilities than Excel does.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Cells keeping a "memory"

    Further to newdoverman's suggestion, if pasting directly into Word causes severe formatting issues, you could paste into Excel first, then into Word (which will give you a table), tidy up, then paste back into Excel. A hassle, but perhaps workable for now?

  8. #8
    Registered User
    Join Date
    04-27-2014
    Location
    Argentina
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Cells keeping a "memory"

    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!

  9. #9
    Registered User
    Join Date
    04-27-2014
    Location
    Argentina
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Cells keeping a "memory"

    ok, finally found a way.
    1. Paste the data from the web page in column A
    2. create a =clean(trim(A1) formula and extend it in column B
    3. copy paste column B as value into column C
    4. select column C, and use "Text to column" function (now the blanks are really blanks)
    5. use "go to special" function to select blanks and press Ctrl + - (to delete the blank cells)
    6. shift cells up
    My cells are now packed with no "empty cell" in the middle.

    Thank you all!
    Last edited by bellfano; 11-08-2016 at 11:44 PM.

  10. #10
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Cells keeping a "memory"

    [QUOTE=bellfano;4518752use "Text to column" function (now the blanks are really blanks)[/QUOTE]
    Great solution - I wish I'd thought of it. Thanks for letting us know.

  11. #11
    Registered User
    Join Date
    04-27-2014
    Location
    Argentina
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Cells keeping a "memory"

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Keeping particular cell blank if the related cells show "0" or "blank"
    By Anuru in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-24-2014, 05:30 AM
  2. Replies: 0
    Last Post: 12-20-2014, 03:44 PM
  3. Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. [SOLVED] "not enough memory" error on workbook.save , but not .close(SaveChanges:=True)
    By sylvainsylvain in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-18-2013, 12:08 PM
  6. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM
  7. Keeping formula in Cells after "data refresh"
    By LarryC in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2008, 04:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1