What is in this blank cell??

[lahatte]

I would like to know what is going on in this Excel 2010 worksheet. I have some time series data with some missing values (empty cells), and when applying a formula to the empty cell I get the result "#VALUE!". Then, if I click in that empty cell and 'delete', then the formula shows "0".

Also, using the Ctrl-DownArrow skips over all those empty cells that cause the formula to show "#VALUE!".

So, what is in those empty cells??

Thanks.

[davesexcel]

Could be a space

[lahatte]

There are no spaces.

[Zer0Cool]

Space or non-print character.

You can use LEN to see what the length of the "string" in the cell is. CODE can be used to return the numeric code for the first character in the cell. If I recall 32 is a space, anything else is likely non print characters.

You could use another column, do CLEAN(TRIM and reference the cell with the current entry in it. This will strip out all spaces from a string except single spaces between words and non print characters. Then just copy/paste special values over your original and remove the CLEAN(TRIM column.

A sample workbook may also be helpful for us to determine the cause.

[lahatte]

Len is 0. Code says "#VALUE!".

It's freaky, man.

The thing is, I WANT the empty cells like this (in another sheet) so I can get "#VALUE!" instead of 0 as the result of the formula I am using. So I was hoping to do a replace of the 'other' empty cells with 'these' empty cells.

[lahatte]

The code function also says "#VALUE!" after I delete the empty cell, so that doesn't seem to acknowledge anything.

[lahatte]

See the attached worksheet. It has the strange cells in it. I highlighted the strange cells...

[sandy666]

with ="" (empty string) in eg.A1 you will get LEN =0 and CODE =#VALUE

edit:
I removed not necessary styles
returned to standard alignment
and got zeroes insted of #VALUE

use ISBLANK() to check "empty" cells

[lahatte]

And the result of the formula A1*1 is zero for an empty string, not "#VALUE!", which these cells are producing.

See the sheet I attached above.

[sandy666]

edit cell and enter and you will see 0 instead of #VAUE

You've 173829 cells with this hidden character

there are two "characters" CR (caret return) and LF (line feed)

[lahatte]

I want to know what that hidden character is so I can replace the 'really empty' cells with it, so that my formula result for empty cells returns the "#VALUE!" instead of 0.

But, I suppose I could do just as well by just replacing blank cells with any old text string.

It's a curious thing though.

[sandy666]

there are two "characters" CR (caret return) and LF (line feed)

you can try copy "empty cell" go to find&replace and so on...
but probably it doesn't work and IMO better will be if you copy data to another place (paste as values), select range with errors (column B, C, D, E , F) and ClearAll from the ribbon

[sandy666]

I did Find&Replace (copied cell and #)
Now you can replace # with blank

here is your file in a half way

[Zer0Cool]

I want to know what that hidden character is so I can replace the 'really empty' cells with it, so that my formula result for empty cells returns the "#VALUE!" instead of 0.

But, I suppose I could do just as well by just replacing blank cells with any old text string.

It's a curious thing though.

I did in F2 filled down:

Please Login or Register  to view this content.

Then in G2 filled down:

Please Login or Register  to view this content.

Interesting results. All but G2 returned 0, G2 still gives #Value!

Did content in column B come from an external source, like a database or copy/paste from a website or something? Often times external systems may use a data type/character not available in Excel and when pasted in its not a recognizable character to Excel (so CODE doesnt work) or LEN (whats the LEN of unknown).

I know that there are a couple ASCII codes I used to search for via VBA to clear out but I dont recall the codes, maybe 182 and something else? These where common characters from database imports that formulas, find/replace, etc couldnt deal with but VBA could.

Also, selecting the "empty" cells (in Col B) and using the Clear all option on the home tab also fixed the calculation results. So clear can flush it out too.

[lahatte]

Hi. Yes. This data comes from a HEC-DSS time series database, exported to be used in Excel.

Thanks for the assistance.

[63falcondude]

Highlight the column with the "strange cells" > Text to Columns > Finish.

This will make them truly blank.

[sandy666]

@63falcondude
But you need to know where the "strange" cells are

[63falcondude]

Sandy, the OP knew where they were. They highlighted them.

In my experience, I have this happen when I copy and paste a formula (that has a result of "") as values.

The cell is only recognized as blank after double clicking the cell (i.e. going into the cell to edit the contents) or through the Text to Columns method.

[sandy666]

@63
see file from post#13 where are #s which OP doesn't highlight
all #s was CR LF

original file from OP

[63falcondude]

I thought that the main request was to make the formulas see the cells as blank.

After reading this:

I want to know what that hidden character is so I can replace the 'really empty' cells with it, so that my formula result for empty cells returns the "#VALUE!" instead of 0.

I now see that this is not the case. That being said, I am sure that there are better ways to get to the result that the OP is after than trying to make specific actually blank cells into something else.

[sandy666]

I used third party software to see what the hell is there, so I know there was CR/LF which is not standard in excel.
IMHO F&R is a good way, but maybe I'm wrong. Who knows?

[Zer0Cool]

I used third party software to see what the hell is there, so I know there was CR/LF which is not standard in excel.
IMHO F&R is a good way, but maybe I'm wrong. Who knows?

I am going to disagree with the software, its certainly possible I am wrong but I further inspected the file. A carriage return at least would be a visible issue within the formula bar, IE you would see an additional line that you could place the cursor on.

If I am not mistaken Alt+Enter puts a carriage return in a cell. If you do this LEN = 1 on it.

I reviewed the XML of the file and I think it fits with my evaluation. When bringing data from an outside system it had content that Excel could not identify the data type or character of. This caused the XML tag for the shared string to break, which the cell and others with the issue referred to.

Please Login or Register  to view this content.

here is a snippet of XML from the Sheet1.xml file. Notice the red text. t=s means it contains a shared string. A shared string is a string that appears in multiple cells. Instead of storing the complete string in each cell individually, it stores the whole string in the sharedStrings.xml and all the cells reference it. The 0 is the index (starting at 0) that the shared string is stored in.

Please Login or Register  to view this content.

Notice how the first si tag has a <t/> closing tag but not an opening tag like the other shared strings.

This is further proven by resolving the issue in the file. If you enter i nthe cells and clear them out so that they are truely empty and review the sharedStrings.xml this entry with the broken tag is removed.

[sandy666]

But LEN is 0, and what?

info about TPS was info only.
I said few posts above : copy cell paste to find then in replace type #, the again F&R # with nothing
why ? because CR/LF not working with nothing in F&R

[Zer0Cool]

But LEN is 0, and what?

Right thats what I am pointing out. If you in a new blank sheet place a carriage return in a cell by itself and do a len pointing to that cell it will return 1, at least it does for me. However in the OP's file it does not. I believe the software you are using likely is unable to ascertain the true character in those cells (since Excel itself cannot) and returns a default assumption that its a CR/LF.

I guess itd be possible to test this by inspecting the shared string of a carriage return or LF in the underlying XML, but from the OP's file its obvious the tag is broken/corrupt since its missing the opening tag for that shared string.

I guess its also wrong of me to assume everyone just knows what I did. My apologies if this is common knowledge.

Brief explanation:

Office files 2007+ (.xlsx, xlsm, docx, docm, pptx, pptm) are all OpenXML file types. Essentially they are just archive files (like a zip) containing a bunch of xml files (and a couple other types) that define the file. To get to the underlying structure you just rename the file to *.zip (ie: File.xlslx = File.zip). I recommend you always do this with a copy of the file. After you rename it you can browse it like a zip file and its contents. OpenXML is well documented and relatively rigid, so its easy to find all the components you need. The files are mostly intuitively named.

So I used the above to look at the xml representing the sheet which led me to see the values in the problem cells had shared strings in them which led me to the shared string that was broken.

Hope this helps

[sandy666]

Problem was how to "clean" strange cells not what is there.
but
you can play with it in different ways.
CR/LF, \r\n, 0x0D 0x0A, ASCII code 13 and ASCII code 10
both are control characters

btw. excel is as wise as its owner

[Zer0Cool]

So, what is in those empty cells??

Thanks.

Problem was how to "clean" strange cells not what is there.

And the Thread title is "What is in this blank cell".

Others had already offered answers to the question the OP didnt ask, ie how to fix the issue, I was simply answering the question they did ask which was why it was the way it was.

[davesexcel]

Would a simple code work? If you want to keep the cells with numbers?

Please Login or Register  to view this content.

[sandy666]

...............
draw

[Zer0Cool]

Would a simple code work? If you want to keep the cells with numbers?

Please Login or Register  to view this content.

This would remove some of their actual data too. Its the same as using find & select | go to special | constants. So they probably shouldnt use this. Id wipe out any manually entered data on the sheet in that whole column.

[davesexcel]

This would remove some of their actual data too. Its the same as using find & select | go to special | constants. So they probably shouldn't use this. Id wipe out any manually entered data on the sheet in that whole column.

Ya, I can't even remember what the question was anymore.