What does "" Actually Equal While Being Used as a Blank?

**Soes Bandit** · 02-01-2017, 12:42 PM

Hi Guys,

I started learning Excel a few months ago to create spreadsheets to help me with a hobby I have. I’ve been using this forum for a couple of months now and it has been a big help... but I wonder if someone could help me with a quick question please? It would be a big help!

I initially had a spreadsheet which contained reoccurring zeros down various columns. To fix this I used the =IF(ISBLANK(cell),””,my formula) for each of these columns. This fixed the problems I was having (the zeros were interfering with certain formulas and giving out incorrect results), and it also improved the aesthetics of my spreadsheet… so far so good.

This led to a problem in which my conditional formatting was being triggered (the cells were set to turn green with a value above zero). I managed to solve this issue by creating another conditional rule that formatted blank cells back to having no colour.

After some research, I discovered that “” is not truly blank and that Excel actually puts a value to it. I have read that the value is a considered a piece of text. After testing the cells with the conditional formatting, it does appear that text triggers it and other figures behave as expected.

Basically I would just like to make sure that having “” in these cells is not going to interfere with any of my calculations? I have tested and everything seems ok as far as I am aware.

Thank you for your time!

**FDibbins** · 02-01-2017, 12:55 PM

hi, welcome to the forum

1. instead if using =IF(ISBLANK(A1),""...
you could also use =IF(A1="",""...
2. "" enters a null into the cell, and while you are correct, the cell is no longer "empty", it shows as "blank".

Depending on the function used (and how it is used), this can be a very effective way to not include cells based on certain conditions.
However, as you have also seen, it can sometimes throw a spanner in the works, but knowing that "" could exist in a range, there are a few ways to cater for thar

In your CF rule, instead of changing back, use an AND() function in the 1st rule to do 2 tests...
=AND(A1<>"",A1>10)

**FDibbins** · 02-01-2017, 12:55 PM

hi, welcome to the forum

1. instead if using =IF(ISBLANK(A1),""...
you could also use =IF(A1="",""...
2. "" enters a null into the cell, and while you are correct, the cell is no longer "empty", it shows as "blank".

Depending on the function used (and how it is used), this can be a very effective way to not include cells based on certain conditions.
However, as you have also seen, it can sometimes throw a spanner in the works, but knowing that "" could exist in a range, there are a few ways to cater for thar

In your CF rule, instead of changing back, use an AND() function in the 1st rule to do 2 tests...
=AND(A1<>"",A1>10)

**Soes Bandit** · 02-04-2017, 03:15 PM

Dear Ford,

Thank you very much for your quick reply. Unfortunately, I have not been able to check up on my post until now, as I have been away!

Thank you for your tips, I will have a play with these and see what I come up with

As for "", your information has helped and I am confident that it isn't causing anymore problems. Its all basic math stuff, nothing special, so I can't see it causing a problem. Like I mentioned, I have also tested the data and everything seems ok.

Thanks again!

**MarvinP** · 02-04-2017, 03:41 PM

Hey Soes,

In Excel the "" for nothing yet or*string of length zero is a lot easier than how vba in Access works. I've spent many hours dealing with NULL vs "" vs " ". See if the link below confuses you more..

http://allenbrowne.com/vba-NothingEmpty.html

**MarvinP** · 02-04-2017, 03:42 PM

Hey Soes,

In Excel the "" for nothing yet or*string of length zero is a lot easier than how vba in Access works. I've spent many hours dealing with NULL vs "" vs " ". See if the link below confuses you more..

http://allenbrowne.com/vba-NothingEmpty.html
or
http://allenbrowne.com/casu-11.html

**Soes Bandit** · 02-04-2017, 04:24 PM

Haha oh dear! It certainly does! I'll have to get my VB head back on.. it's late here now so its Starwars time

I'll have another look tomorrow

What does "" Actually Equal While Being Used as a Blank?

LinkBack

Thread Tools

Rate This Thread

Display

What does "" Actually Equal While Being Used as a Blank?

Re: What does "" Actually Equal While Being Used as a Blank?

Re: What does "" Actually Equal While Being Used as a Blank?

Re: What does "" Actually Equal While Being Used as a Blank?

Re: What does "" Actually Equal While Being Used as a Blank?

Re: What does "" Actually Equal While Being Used as a Blank?

Re: What does "" Actually Equal While Being Used as a Blank?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Keeping particular cell blank if the related cells show "0" or "blank"

[SOLVED] Delete Rows if a cell does not equal "Finished" or "Complete"

[SOLVED] Cell Formula to count time with "Greater Than or Equal to" and "Less Than"

Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

[SOLVED] Delete row if cell in Column N equal "Different" or Column P equals "Blank"

Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank

if statement see "Male" or "Female" returning "Y" otherwise leave blank

Bookmarks

Bookmarks

Posting Permissions