We help IT Professionals succeed at work.

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

Last Modified: 2015-01-16

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.

Watch Question

JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

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

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.
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

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.
This one is on us!
(Get your first solution completely free - no credit card required)


>> 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?


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.
Rory ArchibaldGrand Poobah
Most Valuable Expert 2011
Top Expert 2011

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.


Thanks for all of the responses.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.