False Non-Blank cells

Resolved · Medium Priority · Version 2016

Fri 15 Mar 2019, 08:20 Delegate Jonatan said...

Jonatan has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course

False Non-Blank cells

Hi. I am using a formula =IF('Paste CSV here'!E6="","",TEXT(SUBSTITUTE(LEFT('Paste CSV here'!E6,10),".","/"),"dd/mm/yyyy")) to turn some weirdly formatted text into a date. I then drag the formula across several rows and subsequently copy and paste special values. However after that process the blank cells created by use of if true than "" are being treated by excel as they are not blank and are counted by counta and also when just selecting the cells and being counted in the corner but the same false non blank cells are also being counted by countblank. Any idea how do i make these cells trully Blank?

Fri 15 Mar 2019, 08:45 Trainer Jens said...

RE: False Non-Blank cells

Hi Jonatan,

Thank you for the forum question.

As you have found out "" is not a blank cell.

Try:
=COUNTA(B1:B7)-COUNTIF(B1:B7,"")

Where B1:B7 needs to be changed to your range.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 15 Mar 2019, 11:08 Delegate Jonatan said...

RE: False Non-Blank cells

Hi

But is there w away to turn the blanks generated by "" to be true blank cells either by amending the formula or by using find and replace or any other way? AT the moment the only way i can do it is my going to the cell and hitting DEL.

Thanks

Fri 15 Mar 2019, 13:24 Trainer Jens said...

RE: False Non-Blank cells

Hi Jonatan,

Yes it can be done in a worksheet change event, but you will need to write a VBA code to do it. The cell needs to be deleted if="".

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 15 Mar 2019, 14:30 Delegate Jonatan said...

RE: False Non-Blank cells

Thanks. I have kind of done it. I chnged the formula to If(E3="","X"...) and then ust used find and replace functionality on a range with replacing X with a blank.

Thansk

Fri 15 Mar 2019, 15:25 Trainer Jens said...

RE: False Non-Blank cells

Hi Jonatan,

Well done.

A good solution.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector