# How to stop counting blank cells

#### p86c

##### New Member
So I have pasted data from generated from a piece of software into my excel sheet.

One of the formula returns a value for the number of cells in a column that has data in it.

Unfortunately it is counting all the cells in the column, including the blank ones - so I am guessing they have not transferred as blank.

I find that is I delete each blank cell manually then the count works again - but there are hundreds of these to do.

I think a find an dreplace might work - but what am I finding? and what am I replacing it with...just a blank?

And pointers?
p86c

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### McGuilliam

##### New Member
Could you perhaps post the formula you are using right now? Out of the top of my head, maybe a countif-function where you use <>"" could do the trick.

#### p86c

##### New Member
Could you perhaps post the formula you are using right now? Out of the top of my head, maybe a countif-function where you use <>"" could do the trick.
the formula I used was

=COUNTIFS('A2 Students'!J14:J95,"<>",'A2 Students'!\$D\$14:\$D\$95,"1")

and like I said, it is acting as if the blank cells are not blank, unless I manually delete each one - which obviously, visually, is not making any difference.

#### Fluff

##### MrExcel MVP, Moderator
If you point this formula at a "blank" cell what does it return?
Excel Formula:
``=CODE(A2)``

#### p86c

##### New Member

If you point this formula at a "blank" cell what does it return?
Excel Formula:
``=CODE(A2)``
It says #VALUE!

#### Fluff

##### MrExcel MVP, Moderator
That suggest that the cell is empty, how about these two
Excel Formula:
``````=LEN(A2)
=ISBLANK(A2)``````

#### p86c

##### New Member

That suggest that the cell is empty, how about these two
Excel Formula:
``````=LEN(A2)
=ISBLANK(A2)``````
len gives 0
isblank gives false

#### Fluff

##### MrExcel MVP, Moderator
Ok, that sounds like it might be a null string, which column are these in?
Also are there any formulae in that column?

#### p86c

##### New Member
multiple columns - no formulae though

#### p86c

##### New Member
Ok, that sounds like it might be a null string, which column are these in?
Also are there any formulae in that column?
Found a solution - seems to have worked

Used Find and replace
Found nothing
Replaced with N

then Found "N"
Replaced with nothing

now the formulae are working again

Thanks for taking time to help.

p86c

Replies
3
Views
153
Replies
4
Views
74
Replies
8
Views
139
Replies
20
Views
384
Replies
3
Views
36

1,136,278
Messages
5,674,807
Members
419,526
Latest member
ranjit446

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back