1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Blank cells - unknown characters

Discussion in 'Business Applications' started by bxcfilm, Feb 19, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. bxcfilm

    bxcfilm Thread Starter

    Joined:
    Oct 27, 2010
    Messages:
    66
    When accounting software (in my experience) outputs an account in XL format, I find that the spaces are not necessarily spaces. It's easier to explain if I give you an example. I am attaching a small sample of the output. Columns A - F are from the software, and I have added Column G.

    The formula in Column G4 is just E4-F4, and I have copied that down Column G. This formula evaluates initially to #VALUE!, and the question is Why? I have highlighted cell F4 and pressed Delete, which now allows the formula to evaluate correctly.

    From looking around various help screens, I have found the standard answer that there must be some non-numeric character in the apparently blank cell. However, if you highlight for example cell F5 and press F2, there is no sign of a space, or any other character. However, just to make matters more complicated, if after pressing F2, you press Delete and then Return, the formula evaluates correctly!

    Something has obviously been deleted, but what? If it was just a few cells, I could press Delete on each and be done. But I sometimes have hundreds of rows of output to deal with each month, which makes that impractical. At the moment, I get around it by inserting extra columns and using =IF(ISNUMBER(..... to copy across the "real" numbers and put a proper blank where there is no number. But that's clunky and it would be a lot easier if I had a way of converting these apparently blank cells to actual blank cells.

    I couldn't find anything in the formatting of the cells which looks unusual, so I can't change the formatting. And I can't use Search & Replace, because I don't know what I'm searching for. Although I use Win XP and XL 2010 at home, I have previously found the same problem on XL97 and 2000 on various PC's at the office, over a period of more than 10 years.

    I would appreciate any suggestions.
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,251
    First Name:
    Wayne
    that should work - just tried on your sample

    if you do an isbalnk() then you get a false
    I tried trim() and value() both failed to work

    so I used find and replace twice

    first i left the find empty - so it would find all the blanks - included those non-blank cells that appear blank
    and used a unusual word - fred in the replace

    all the blanks now have fred
    i then used find and replace again
    and this time
    find fred
    and left the replace blank
    and it worked on all the cells and now the formulas work

    not sure its the correct solution, but at least a work around

    i suspect the blank cells has a null in - not sure how to show that
    isblank() = false and len() = 0
     
  3. bxcfilm

    bxcfilm Thread Starter

    Joined:
    Oct 27, 2010
    Messages:
    66
    That is fascinating. I have been using spreadsheets on and off for 30 years, and I've never before felt the need to search for [nothing]! But that does indeed work. And it's quick to do, as well.

    On the face of it, replacing [nothing] with [fred], and then replacing [fred] with [nothing] should put the cells back the way they started. But it doesn't...

    I agree that it doesn't look like the "correct" solution, but it is a very practical one.

    Many thanks for your help.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,251
    First Name:
    Wayne
    your welcome, researched and cant find any other solution, other than a macro to do the clear - had this type of issue before when importing data - often numbers appear as text and value() fixes that - and had this issue a couple of times and thats how i resolved before

    hopefully one of the excel gurus will pick up and reply
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090186

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice