Avatar of WeThotUWasAToad
 asked on

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


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


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?


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:
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.

Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

In my experience, text will only spill over into the adjacent right cell when the adjacent right cell is empty. I think Excel knows about empty cells and I have never heard of mystery characters. I don't think there are any.

Excel has worked like this for as long as I have used Excel (Windows 3).
Wayne Taylor (webtubbs)

As long as there is a formula in a cell, it can never be "empty". The Value of a cell can be "empty" (by returning ""), but the cell itself is not.

Thanks for the responses:

>> …text will only spill over into the adjacent right cell when the adjacent right cell is empty.
…mystery characters. I don't think there are any.
I completely agree with your statement — including the final part of it. In other words, I also don't believe there is literally some "mystery character". I used that terminology in my post simply to refer to whatever characteristic or functionality causes the cell to act like it contains a character even though it doesn't.

Since we both agree that, "text will only spill over into the adjacent right cell when the adjacent right cell is empty", then what is it that is blocking the spillover when there is nothing in the cell? This is exactly the point of my question.

Now, still addressing the quote above but also the following:

>> As long as there is a formula in a cell, it can never be "empty". The Value of a cell can be "empty" (by returning ""), but the cell itself is not.
For example, figure 2 shows cell B2 contains a formula purposely designed to return nothing. It says, if cell B1 contains any kind of entry, to return "yes", but if not, to return "", which supposedly means "nothing" (or "empty" or "blank"), right?

So if it indeed returns "", then what is blocking the spillover from A2 as shown in figure 3? Well, one could say that even though the returned value is "", cell B2 does contain a formula and therefore it's the presence of formula which is blocking the spillover. OK, fair enough.

But what about when B2 is copied and then pasted into another cell using Paste Value? That's supposed to remove formulas and paste only the value that the formula produced, right? Well, if the formula in B2 produces nothing then shouldn't nothing end up in in cells B5 & B6 in figure 4? That's what you'd think but as shown in figure 4 & figure 5, spillover is blocked which suggests that something is still present.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

It is reasonable if you Copy and then Paste Special Values that something is left over from the Paste.

I tried a new sheet and ANY time nothing was in the right adjacent cell, text would spill over. Anything (including non visible stuff) prevents the spill over.

spillover is blocked which suggests that something is still present.  <-- Yes, but it could be none-visible stuff. I do not believe there is a mystery character.
Wayne Taylor (webtubbs)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

>> Nothing and Empty are not the same thing. So after the paste operation, the cell is not empty as it contains a string of 0 length.
That is an extremely helpful statement. It gives me both the terminology and explanation to account for what occurs in Excel.

So if ("") in a formula results in "a string of 0 length", is there anything which results in empty or any way to get an empty outcome?

Wayne Taylor (webtubbs)

No. The cell will never be empty because it contains the formula and a formula will always return something. The only way to set a cell to empty is to use the Delete key, or with Range.ClearContents in VBA.

Can I ask what the end purpose is? We may be able to come up with an alternative.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rory Archibald

Unfortunately there is (as yet - hint, Microsoft!) no equivalent of PowerPivot's BLANK() function in Excel.

Looks like this is(/was) over. At risk of beating it to death, there is an overlaying concept of printable character vs nonprintable (other) not to mention overlay vs wrap and what it is that defines character (ex: ascii). Within Excel there are some rules for handling, which can be to ignore or treat specially.

In Excel, cell may have no entry, nothing. It may have content intended to be text (ex string) string or number. They are generally justified, text to left, number to right of cell. Strings are often having length defined by value of 1st character. To find value of a cell code needs pointer. To achieve lack of pointer tradition is to make it <nul>, 'not entered' (or 'no entry').

Here is "ASCII Code - The extended ASCII table"
In Decimal:

 0 = ""  = <nul>   [or <null>]
32 = " " = <space> [or <blank>]
48 = "0" = <zero> [lowest of numeric, of alpha-numeric]
65 = "A" = 1st letter of alphabet

    "0" <"A"

Where character is nonprintable, it may 'look like' a nul, or blank or square or "¿", it may be 'processed' or ignored, it may be used input only (<backspace>) or output only (<bel>|<beep>) or symbolic (½,<,>,≈,..).

Last, as an example, should you decide to force a cell or column to not overlay the subsequent columns, you can not only choose to 'wrap' content, you can select where to wrap : place curser at position of text string, while holding <alt> key press <enter>. To undo later, you can use <BS> or <DEL> from <KB>.

From original question:
> truly leaves a blank result in a cell?

This is question for user/designer. There are two basic choices. Nul vs Space.

You may want to write formula to check first character. If it has the space, it has the 1st character.
Where you may have a column that is mainly answered by a variety of {y,yes,yeah, n,N,no, nope, nah,nyet, nada, N/A,NA...) and the first char determines the basic answer. For data entry, it may also be simpler to permit them the use of the space key.

When erasing a cell, either a blank space or nul are used, the former can be more visible, the latter used more often these days. It can depend on what other applications, uses can be had for data, such as for a csv or Access or Word or EE.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

Thanks for all of the responses.