Solved Blank Cells That Are Not Blank

February 11, 2013 at 03:20:42
Specs: Windows 7, 2.128 GHz / 1014 MB
Hi has anybody had this problem

I have cells that appear blank or empty when in fact are not.

The cell is empty but I get these results

=ISTEXT Returns TRUE

"=IF(ISBLANK(Data!CA292),"ISBLANK") RETURNS FALSE

If I delete the contents of CA292 which appear empty I get the opposite of above

I have tried

=CLEAN
=TRIM
=CV292&""&CW292

I cant find anything else on Microsoft Help that seems to help


See More: Blank Cells That Are Not Blank

Report •


✔ Best Answer
February 11, 2013 at 05:45:31
With =TRIM(), The spaces before and after the text will be removed completely.
Multiple spaces within the text will be trimmed to a single space.

Is there some reason you need to trim out all of the spaces,
after all, Excel will treat the whole contents of the cell as Text?

Are you trying to match up columns of data?

EDIT ADDED:
Sorry, forgot to give you the formula.

Try this: =SUBSTITUTE(A1," ","")

MIKE

http://www.skeptic.com/



#1
February 11, 2013 at 03:58:11
A Space Character is a valid character to Excel.
So if you have a Space in the cell, it is not blank.

Try this formula: =TRIM(CLEAN(A1))

See how that works.

MIKE

http://www.skeptic.com/


Report •

#2
February 11, 2013 at 04:15:53
You specifically mentioned CA292.

What have you used that cell for in the past? Is there a formula in it? Did you download data into the cell or into a range of cells that included that CA292?

Try =CODE(CA292) and tell us what it returns.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 11, 2013 at 04:17:17
Ref Blank Cells That Are Not Blank

Hi Thanks for the reply

I used =TRIM(CLEAN(A1)) and then cut and paste A1 using Paste Special Values back into the original cell but got the same result

I have also tried copying the entire column into another workbook to see if that made any difference

Thanks


Report •

Related Solutions

#4
February 11, 2013 at 04:34:35
Ref Blank Cells That Are Not Blank

Hi
"=CODE(CA292) returns #VALUE!

Column CA has 12,000 email address, some are blank. The data in col CA has not been used for any calculation or formulas but some of the data has been cut and paste from the internet but most came from data given to us, if that helps

Thanks


Report •

#5
February 11, 2013 at 04:50:36
If the data is all TEXT what is it your trying to do?

MIKE

http://www.skeptic.com/


Report •

#6
February 11, 2013 at 04:59:16
=ISTEXT Returns TRUE

"=IF(ISBLANK(Data!CA292),"ISBLANK") RETURNS FALSE

If I delete the contents of CA292 which appear empty I get the opposite of above

This is how it is supposed to work:

ISTEXT will return TRUE if there is a Space Character in the cell, Excel treats a space as a valid character.

ISBLANK will return FALSE if there is a Space Character in the cell, because the cell is NOT blank, there is a space character in the cell.

When you DELETE the contents of the cell, then the opposite is now True.

MIKE

http://www.skeptic.com/


Report •

#7
February 11, 2013 at 05:04:17
ref Blank Cells That Are Not Blank

Hi

We email people and I have to check if the email address in Col Ca has been emailed if not email them so I check if emailed out data = address on our database

Thanks


Report •

#8
February 11, 2013 at 05:14:50
ISTEXT will return TRUE if there is a Space Character in the cell, Excel treats a space as a valid character.

To be more specific:

ISTEXT will return TRUE if there is a Space Character in the cell,
because Excel treats a space as a valid TEXT character.

MIKE

http://www.skeptic.com/


Report •

#9
February 11, 2013 at 05:19:49
Just to see how confusing all this can get,

In cell A1 enter 1
in cell B1 enter =ISTEXT(A1) it should return FALSE

Now in cell A1 enter the number 1, a space, then the number 2
and see what you get.

In Excel sometimes things aren't always as they appear.

MIKE

http://www.skeptic.com/


Report •

#10
February 11, 2013 at 05:36:56
Ref Blank Cells That Are Not Blank

Thanks for that

Yes I get TRUE to 1 2

So that means I must have spaces in aprox 3000 blank cells, deleting the cell I delete the space so if I use =TRIM(CLEAN(A1)) and then cut and paste using Special Values I am simply copying back the space ?

If that's the case once I Cleaned and Trimmed how do I get the data back into the right col/cell without copying back the space

Many Many thanks


Report •

#11
February 11, 2013 at 05:45:31
✔ Best Answer
With =TRIM(), The spaces before and after the text will be removed completely.
Multiple spaces within the text will be trimmed to a single space.

Is there some reason you need to trim out all of the spaces,
after all, Excel will treat the whole contents of the cell as Text?

Are you trying to match up columns of data?

EDIT ADDED:
Sorry, forgot to give you the formula.

Try this: =SUBSTITUTE(A1," ","")

MIKE

http://www.skeptic.com/


Report •

#12
February 11, 2013 at 06:09:20
Ref Blank Cells That Are Not Blank

Hi Mike Many Thanks

This "=IF(ISBLANK(Data!BL284),"N",Data!BL284) Checks to see if we have an email address. It actually returns nothing if there is no email address because as you have explained there must be a space in BL284 I then check if there was an email address it matches an emailed address by simply "=IF(C284=T284,"Y","N")

I have never come up against this problem its very strange

Thanks
I h


Report •

#13
February 11, 2013 at 10:07:11
You could replace: =IF(C284=T284,"Y","N")

With this: =EXACT(C284,T284)returns True or False
=EXACT() Function compares two items of text and determine whether they are exactly the same.
The case of the characters is taken into account, and only words which are spelt the same and which have upper and lower case characters in the same position will be considered as equal.

the data has been cut and paste from the internet but most came from data given to us

Any time you get data from an outside source, especially from the net, it is a good
idea to first clean it up with the =TRIM(CLEAN)) at a minimum, there can be a number of different unprintable characters imbedded in the info.
If memory serves me, the first 32 characters in the ASCII code are unprintables.

Glad you got your problem solved.

MIKE

http://www.skeptic.com/


Report •

#14
February 12, 2013 at 04:23:35
Blank Cells That Are Not Blank

Many thanks for your help it was very helpful

Thanks


Report •


Ask Question