CODEWITHSUNDEEP
javaexcelms-office
Filip
Filip

Reputation: 2344

Empty value in Excel cell?

I have a java program which receives .xlsx (Excel file) as an input.

Somehow I manage to create some, lets say, "strange empty state" in one of the cells crucial for work of my program. By that, I mean that cell looks perfectly empty and cause program to has behavior A.

However, If I mark that disputable cell, press "delete" button (try to empty content) from keyboard upon it and save document, program has different behavior B.

Any idea which value could be initially in the cell which looks perfectly empty? And how is possible that simple deleting changes content of the cell? Any idea what to do?

Upvotes: 0

Views: 119

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

There are at least three different ways in a cell can appear empty, but actually not be empty.

Start with a clean, new, worksheet and in cell B1 enter:

=ISBLANK(A1)

then copy B1 to B2 and B3 They will all show True

Then in A1 enter the formula:

=""

Copy A1 and PasteSpecialValues onto cell A2

Then enter a single quote character in cell A3

The cells in column A will appear empty, but the cells in column B will now show False!

EDIT#1

From VBA pick a cell and run:

Sub GhostFinder()

If IsEmpty(ActiveCell) Then
    MsgBox "genuine blank!"
    Exit Sub
End If
With ActiveCell
If Len(.Value) > 0 Then
    MsgBox "something is there"
    Exit Sub
End If
If .HasFormula Then
    MsgBox "formula returning blank"
    Exit Sub
End If
If .PrefixCharacter <> "" Then
    MsgBox "Prefix character present"
    Exit Sub
End If
End With
MsgBox "Null present"
End Sub

Upvotes: 1

Related Questions