Excel file with "ghost" data in cells 1

[JG2828]

I was working on an excel file and was getting some incorrect results with formulas. After some troubleshooting I found what seemed to be empty cells that have some sort of "ghost" data in them. I was able to copy this ghost data to the attached spreadsheet in Cells A2 and A3. Whatever is in there affects formulas, for example "Count" and > Greater than (as used in the B column).
The ghost data can be cleared by either hitting delete on the cell or double clicking on the cell and hitting enter.
Can anyone tell me what is in cells A2 and A3 and possibly how it got there?

 

[3DDave]

I looked at the xml file and it looks like a broken index to the Shared Strings portion of the xlsx file. I don't know what Excel is supposed to do when the property is an index but no index value is given.

It isn't clear how it can be reproduced except by editing the xml file directly.

 

[JG2828]

Thanks for that 3DDave.
I finally figured out how the ghost cells were created.
If you use an IF formula to clear out zeros, using double quotes like this: =IF(A10>0,A10,"")
Then you copy and paste the VALUES from the results of that formula, you end up with the broken shared strings index like you said.
I wonder if there is some way to report this to Microsoft, I don't think it would be expected behavior. "" should not be greater than 0.
Do you have a better way to leave an empty cell when using an IF statement? Something other than ""? Leaving it blank after the comma will give a 0, not an empty cell.

Thanks

 

[cowski]

https://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel

The link above has some discussion on the topic that might be helpful.

 

[JG2828]

Thank you cowski. I actually just finished reading that entire discussion. It looks like this problem has been around for a long time. I guess I just never came across it until today.
I guess I just have to be very careful in the future when using the ="" in any formulas. It does not return an truly empty cell and may have negative affects later if someone uses copy and paste values. ie =if(xx>0), or selecting the cells and looking at the count at the bottom footer. Strangely the =count(xx:xx) formula doesn't count them, it only counts numbers, not text or empty strings.

I used the following VBA script to clear the ("") empty strings out of my worksheets. Just select the range you want to fix and run the VBA.

Sub RefreshCells()
For Each r In Selection
r.Formula = r.Formula
Next
End Sub

 

[IRstuff]

Huh, interesting; there are a couple of Excel tests that do show that those cells are different. Note that using "" essentially is an empty string, so a cell with "" ought to be different than a truly empty cell, as shown by testing the cell in Excel itself:

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[JG2828]

I just came across this Microsoft user voice link. It looks like Microsoft has no plans to fix the issue and give us a way to return a nul or empty cell from a formula.

https://excel.uservoice.com/forums/...5062-give-us-a-proper-null-worksheet-function

 

[JG2828]

Another Microsoft post on the topic.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/isblank-function-return-false

 

[3DDave]

Thanks for the rabbit hole;

https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet

is especially deep. Not only is the subject a fun one, there's this part of a comment that's a standout:

"My real beef with those people is that they do not take MS Office feedback seriously. I don't know how many feedback I have left on the MSDN KBs in the last couple of years but none of them have been actioned! It's as if they bloody don't care!"

 

[SkipVought]

I put some expressions in the adjacent columns as indicated in row 1, FYI.

Notice that your rows 2&3 my rows 3&4, that if you need to identify empty cells then if you select (Blanks) in a filter then those 4 rows will be displayed, even though ISBLANK() indicates FALSE.

How it got that way??? Maybe you could shed some light on how you got to this point.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[3DDave]

XX:XX is a substitute for A1:A2. It's a range.

 

[Denial]

Some time ago, needing to get to the bottom of this sort of thing, I created for myself a spreadsheet that applied all Excel's ISxxxx() functions to as many different types of cell entries I could think of.[ ] I later extended it to include VBA's ISxxxx() functions.[ ] See attached.[ ] (It is in a zip file because - for obvious reasons - it includes VBA code.)

 

[JG2828]

Thanks 3DDave,
you are correct, I was attempting to reference a range while being to lazy to actually type a real one.

Thanks Skip,
I figure out the issue in my reply. It is an empty string. One way to get into the situation is by using the formula ="" Then copy / pasting the result as values.

Thanks Denial,
That is a very useful spreadsheet. I'll need to study it for a bit and look up what some of the is functions do.