+ Reply to Thread
Results 1 to 30 of 30

What is in this blank cell??

  1. #1
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2021
    Posts
    19

    What is in this blank cell??

    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.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: What is in this blank cell??

    Could be a space

  3. #3
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2021
    Posts
    19

    Re: What is in this blank cell??

    There are no spaces.

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: What is in this blank cell??

    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.

  5. #5
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2021
    Posts
    19

    Re: What is in this blank cell??

    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.

  6. #6
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2021
    Posts
    19

    Re: What is in this blank cell??

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

  7. #7
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2021
    Posts
    19

    Re: What is in this blank cell??

    See the attached worksheet. It has the strange cells in it. I highlighted the strange cells...
    Attached Files Attached Files
    Last edited by lahatte; 10-19-2017 at 02:36 PM.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

    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
    Last edited by sandy666; 10-19-2017 at 02:37 PM.

  9. #9
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2021
    Posts
    19

    Re: What is in this blank cell??

    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.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

    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)
    Last edited by sandy666; 10-19-2017 at 02:48 PM.

  11. #11
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2021
    Posts
    19

    Re: What is in this blank cell??

    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.

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

    Quote Originally Posted by sandy666 View Post
    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

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

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

    here is your file in a half way
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: What is in this blank cell??

    Quote Originally Posted by lahatte View Post
    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:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in G2 filled down:

    Formula: copy to clipboard
    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.

  15. #15
    Registered User
    Join Date
    10-19-2017
    Location
    Mississippi
    MS-Off Ver
    2021
    Posts
    19

    Re: What is in this blank cell??

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

    Thanks for the assistance.

  16. #16
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: What is in this blank cell??

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

    This will make them truly blank.

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

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

  18. #18
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: What is in this blank cell??

    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.

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

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

    original file from OP

  20. #20
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: What is in this blank cell??

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

    After reading this:

    Quote Originally Posted by lahatte View Post
    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.

  21. #21
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

    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?

  22. #22
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: What is in this blank cell??

    Quote Originally Posted by sandy666 View Post
    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.

  23. #23
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

    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
    Last edited by sandy666; 10-19-2017 at 06:47 PM.

  24. #24
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: What is in this blank cell??

    Quote Originally Posted by sandy666 View Post
    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

  25. #25
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

    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

  26. #26
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: What is in this blank cell??

    Quote Originally Posted by lahatte View Post
    So, what is in those empty cells??

    Thanks.
    Quote Originally Posted by sandy666 View Post
    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.

  27. #27
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: What is in this blank cell??

    Would a simple code work? If you want to keep the cells with numbers?
    Please Login or Register  to view this content.

  28. #28
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: What is in this blank cell??

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

  29. #29
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: What is in this blank cell??

    Quote Originally Posted by davesexcel View Post
    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.

  30. #30
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: What is in this blank cell??

    Quote Originally Posted by Zer0Cool View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 06-29-2016, 03:44 AM
  2. [SOLVED] IF Cell is blank, calculate, if cell isn't blank, leave blank
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2015, 03:54 PM
  3. Calculate differnce between 2 dates, but if 1 date cell is blank leave cell blank
    By Vicious00013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2014, 11:31 PM
  4. [SOLVED] Concatenate If Blank - remove blank line if first cell is blank
    By ker9 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-27-2014, 02:14 PM
  5. Replies: 3
    Last Post: 01-23-2014, 12:19 PM
  6. [SOLVED] Cell referenced in formula has no information displayed (shows blank), return blank cell
    By nunayobinezz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-07-2013, 04:51 PM
  7. If cell blank OR another cell blank then show blank, if not display value
    By stevop622 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-09-2013, 04:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1