Happy New Year!
Can someone explain what Excel puts into a cell to camouflage the cell as being empty or blank when the cell really is not (empty). Also, is there a way to write a formula which truly leaves a blank result in a cell?
For example, suppose in a spreadsheet you enter the following sentence:
"This is a sentence which spills over into the next column."
into cell A2 as shown here:
Next, suppose you enter the formula
into the adjacent cell, B2 has here:
note from the above formula that because no text is present in cell B1, the formula in B2 displays "".
And "" represents "blank" or "no entry", right?
The next screenshot shows that something is indeed present in cell B2 else why is the spillover sentence present in cell A2 now cut off? That only happens when something is present in the adjacent cell, right?
The identity of that "something" (which I will represent with small box "□") is what I'm trying to determine.
Here are some additional characteristics of □:
Notice that when the sentence in A2 and □ in B2 are copied and then Paste/Value is applied in rows 5 & 6:
the mystery character □ is included in the paste as the sentence in both rows are still cut off.
And when the cell containing one of the pasted □'s is selected and the cursor is placed in the formula bar, nothing is displayed as being present and pressing the backspace key results in no movement of the cursor in the formula bar as would be the case if a space or some other invisible character was present:
However, moving to some other cell reveals that something has occurred to allow the adjacent sentence to a again spill into adjacent columns.
In fact, simply clicking in the formula bar of a cell containing □ (without backspacing or deleting or any other maneuvers) — in this case, B6:
— seems sufficient to remove □ and thus cause the cell to be empty:
Two more characteristics are shown in the following screenshots where the original sentence in A2 and □ in B2 have been Paste/Valued in columns A & B of rows 9-12. The first of these shows the results if some empty cell (B14) is copied and then pasted into B9:B12:
Doing so effectively removes the mystery character □ which was previously present in B9:B12
And finally, □ can apparently be copy/pasted as shown here:
where cell B9 was copied and then pasted into the "Find what:" field of the Find and Replace box and then, successfully replaced with a vertical bar ("|"):
Note that although five cells were selected for the Replace step, the vertical bar ("|") ended up only in the first three which presumably contained □ thus effectively demonstrating that cells containing this mystery character (□) are quite clearly not the same as empty or blank cells.