asked on
Excel character camouflaged as "empty" or "blank" cell
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:
![1]()
Next, suppose you enter the formula
=IF(B1<>"","yes","")
into the adjacent cell, B2 has here:
![2]()
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?
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?
![3]()
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:
![4]()
the mystery character □ is included in the paste as the sentence in both rows are still cut off.
![5]()
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:
![6]()
However, moving to some other cell reveals that something has occurred to allow the adjacent sentence to a again spill into adjacent columns.
![7]()
In 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:
![10]()
Doing so effectively removes the mystery character □ which was previously present in B9:B12
![11]()
And finally, □ can apparently be copy/pasted as shown here:
![12]()
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 ("|"):
![13]()
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.
Thanks
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=IF(B1<>"","yes","")
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?
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?
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.Thanks
Microsoft Excel
Last Comment
WeThotUWasAToad
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.
ASKER
Thanks for the responses:
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:
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.
>> …text will only spill over into the adjacent right cell when the adjacent right cell is empty.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.
…mystery characters. I don't think there are any.
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.
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
>> 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
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.
Can I ask what the end purpose is? We may be able to come up with an alternative.
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"
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>.
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>.
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.
> 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.
ASKER
Thanks for all of the responses.
Excel has worked like this for as long as I have used Excel (Windows 3).