Blank cell which is not blank?

bxcfilm

New Member
Joined
Apr 15, 2004
Messages
12
Office Version
  1. 2010
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi,

Most probably, you are dealing with CHAR(160) or CHAR(32) ... which need to be removed ...

HTH
 
Upvote 0
VoG's answer led me to Chip Pearson's CellView Add In, but that just shows no characters at all in the "blank" cells. Try it for yourselves.
 
Upvote 0
I've been offered a workaround in another forum. It doesn't exactly make total sense, but it works.

Highlight cells E5 - F19
Ctrl-H (Search & Replace)
Find [nothing]
Replace with xyz or any character string which doesn't otherwise appear in the highlighted area
Press Replace All
Ctrl-H again
Find xyz
Replace with [nothing]
Press Replace All

That ought to put the cells back the way they started, but it does not.

That is weird.
 
Upvote 0
use =CODE(a1) to find the correct code which is causing trouble. If it is really blank you should get #VALUE!.
 
Upvote 0
Ok, I opened the file and it really is strange. Anyway, there are several workarounds. You know the search & replace routine. You could also adjust your formula to not calculate "" (i.e. IF(F1="" and so on), or use text to columns (my preference as it doesn't alter the data in any way).
 
Upvote 0

Forum statistics

Threads
1,223,405
Messages
6,171,925
Members
452,433
Latest member
Woodchuck76

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top