troubleshooting Question

Excel character camouflaged as "empty" or "blank" cell

Avatar of WeThotUWasAToad
WeThotUWasAToad asked on
Microsoft Excel
11 Comments1 Solution6266 ViewsLast Modified:
Hello,

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:
1Next, suppose you enter the formula

        =IF(B1<>"","yes","")

into the adjacent cell, B2 has here:
2note 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?

Wrong.

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?
3The 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:
4the mystery character □ is included in the paste  as the sentence in both rows are still cut off.
5And 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:
6However, moving to some other cell reveals that something has occurred to allow the adjacent sentence to a again spill into adjacent columns.
7In fact, simply clicking in the formula bar of a cell containing □ (without backspacing or deleting or any other maneuvers) — in this case, B6:
8— seems sufficient to remove □ and thus cause the cell to be empty:
9
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:
10Doing so effectively removes the mystery character □ which was previously present in B9:B12
11And finally, □ can apparently be copy/pasted as shown here:
12where 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  ("|"):
13Note 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.

Thanks
ASKER CERTIFIED SOLUTION
Wayne Taylor (webtubbs)

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros