Solved

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

Posted on 2015-01-01
11
450 Views
Last Modified: 2015-01-16
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
0
Comment
Question by:WeThotUWasAToad
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 90

Expert Comment

by:John Hurst
ID: 40526901
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).
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40526927
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.
0
 

Author Comment

by:WeThotUWasAToad
ID: 40527078
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.
0
 
LVL 90

Expert Comment

by:John Hurst
ID: 40527082
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.
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 40527084
We need to clear up the definition of "empty". In Excel "empty" is used in a couple places....

An empty string, defined as double quotation marks (""), or a string of zero length.
An empty cell, which is literally empty in that it contains no formula or value.

When you are pasting the values into other cells, you are effectively pasting an empty string, not nothing. 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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:WeThotUWasAToad
ID: 40527135
>> 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?

Thanks
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40527154
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40527315
Unfortunately there is (as yet - hint, Microsoft!) no equivalent of PowerPivot's BLANK() function in Excel.
0
 
LVL 24

Expert Comment

by:SunBow
ID: 40529806
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"
http://www.ascii-code.com/
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

         so
    "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>.
0
 
LVL 24

Expert Comment

by:SunBow
ID: 40529824
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.
0
 

Author Closing Comment

by:WeThotUWasAToad
ID: 40554602
Thanks for all of the responses.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now