none
Why do I get the value like this? RRS feed

  • Question

  • I get the value "" as below

    : Value2 : "" : Variant/String
    

    then I can not use IsEmpty for that cell. So I added a code for the ""

    I just wonder what that "" is. Is that null or any kind of string?, And the type returns 2 (text)

    Many thanks .


    • Edited by jjuunn Tuesday, August 28, 2018 5:17 AM
    Tuesday, August 28, 2018 5:05 AM

Answers

  • "" is a text string of length 0, in other words an empty text string.

    The "" can be the result of a formula. For example, if A1 contains the number 5, the formula

    =IF(A1<0,"Negative","")

    will return "". Although the cell with the formula appears to be empty, Excel considers it not to be empty.

    Cells can also contain "" if the data have been imported or copied from another source, for example from a web page.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by jjuunn Tuesday, August 28, 2018 10:21 AM
    Tuesday, August 28, 2018 8:16 AM

All replies

  • "" is a text string of length 0, in other words an empty text string.

    The "" can be the result of a formula. For example, if A1 contains the number 5, the formula

    =IF(A1<0,"Negative","")

    will return "". Although the cell with the formula appears to be empty, Excel considers it not to be empty.

    Cells can also contain "" if the data have been imported or copied from another source, for example from a web page.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by jjuunn Tuesday, August 28, 2018 10:21 AM
    Tuesday, August 28, 2018 8:16 AM
  • Thank you so much for clear answer.

    Is there any easy way you like to use to clean this cells?

    I tried some methods and worked for me.

       For Each cell In Intersect(Selection, _
          Selection.SpecialCells(xlConstants, xlTextValues))
      
        cell.Value = Application.Trim(cell.Value)
        cell.Value = Application.WorksheetFunction.clean(cell.Value)
       Next cell

    Thank you again.

    Tuesday, August 28, 2018 10:51 AM
  • You can simplify

    Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues))

    to

    Selection.SpecialCells(xlConstants, xlTextValues)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 28, 2018 11:04 AM