The cell is NOT blank...Help!

[ibddude]

I've imported an Excell sheet which started in Seibel and in a column full of dates, there are blank cells (nothing in them) BUT a ISBLANK test returns false. Sure enough if I go to the cell and delete the contents then the function return TRUE. This process will happen often and I'd like to have these cells recognized as blank...it there another test (perhaps it's a line feed or carriage retun character?)

Also, the ones with "dates" seem to be formatted as dates but I can't use them in a logical test to say todays date (lareger than or smaller than) becasue they are not shwoing as the date serial number....any ideas here would be great as well!

Thanks so muc

 

[AlphaFrog]

This should convert your text dates to serial dates. It may or may not fix the IsBlank issue.

• Select your dates column
• Select from the menu Data\Text to Columns
  • Select Delimited and Next
  • Deselect all delimeters and select Next
  • Select Column data format: Date
  • Finish

 

[Scott Huish]

The "empty" cell returning FALSE, is usually caused when you have a formula that returns a formula blank. ""

Then if you copy and Paste Special Values, this will put the value of the empty string in the cell, so Excel does not view this as blank.

If they contain dates, you could use a ISNUMBER test or an is = "" test

 

[Joe4]

Many times, data copied/imported from another source will have special "blank" characters in them. One way to find out what they are is to use a formula like this (if the cell in question was A1):

=CODE(LEFT(A1,1))

Then lookup the value it returns on an ASCII table like this: http://www.ascii-code.com/

That will tell you exactly what you have in that space, so you can work on removing it (i.e. via Search & Replace, Macros, etc).

 

[ibddude]

Thanks. I will keep this handy to determine what ASCII character it is.