Cell with 0 characters but FALSE =ISBLANK

**SHI.NL** · 04-19-2018, 05:30 AM

Hi,

I have a cell (actually many) in an export report that is not blank because ISBLANK returns False but LEN returns 0.
I would like to use this specific cell in a SUM but that is now impossible because it returns VALUE.

I can simply delete the (invisible) contents from this cell, but this export will occur monthly and will not be done by me, so I am trying to keep steps to process it all limited.

What can I do to be able to use this cell in a SUM, in combination with a lookup - because the cell is not always in the same column? Which formula can help?

Thanks.

**Pete_UK** · 04-19-2018, 05:38 AM

If the data has been imported then you might have cells which contain the non-breaking space character (with a code of 160). You could use Find & Replace to remove those, and instruct your users in how to do this.

Hope this helps.

Pete

**SHI.NL** · 04-19-2018, 05:38 AM

Never mind, I found something:

=IF(LEN(HLOOKUP(A1,Sheet2!$A:$AL,2,0))<1,"0",HLOOKUP(A1,Sheet2!$A:$AL,2,0))

**Pete_UK** · 04-19-2018, 05:40 AM

You might want to remove the quotes from around the zero in the middle - they will cause that value to be text (which might be okay for you).

Pete

**SHI.NL** · 04-19-2018, 05:42 AM

Originally Posted by Pete_UK

If the data has been imported then you might have cells which contain the non-breaking space character (with a code of 160). You could use Find & Replace to remove those, and instruct your users in how to do this.

Hope this helps.

Pete

Thanks Pete! I found something...

**SHI.NL** · 04-19-2018, 05:42 AM

Originally Posted by Pete_UK

You might want to remove the quotes from around the zero in the middle - they will cause that value to be text (which might be okay for you).

Pete

Hahaha yes, I happen to just have done that a minute ago! Thanks for your detail oriented eye!!!!!

Cell with 0 characters but FALSE =ISBLANK

LinkBack

Thread Tools

Rate This Thread

Display

Cell with 0 characters but FALSE =ISBLANK

Re: Cell with 0 characters but FALSE =ISBLANK

Re: Cell with 0 characters but FALSE =ISBLANK

Re: Cell with 0 characters but FALSE =ISBLANK

Re: Cell with 0 characters but FALSE =ISBLANK

Re: Cell with 0 characters but FALSE =ISBLANK

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If function true then remove leading 8 characters false enter as is.

[SOLVED] With same contract number, any cell in B False, then All cells false in C,

[SOLVED] Writing =IF(ISBLANK($H4), "", VLOOKUP($H4,DCID[#All], 3, FALSE)) to a cell and work

Help with ISBLANK function referring to a cell with another ISBLANK formula

for each foundcell if offset isblank = false

If statements: Non-numerical characters returning false when true

C1 appears blank, but ISBLANK(c1) returns false

Bookmarks

Bookmarks

Posting Permissions