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. Here is a small sample of the output.
https://www.dropbox.com/s/egh15zk5kon61nz/Blank cell problem.xls
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.
https://www.dropbox.com/s/egh15zk5kon61nz/Blank cell problem.xls
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.
