Clear cell contents if cell's value is "" without using VBA

Question

I've a column A with 'empty cell' or 'number stored as text'. I need to get 'empty cell' or 'numbers values' to create a graph.

What I usually do is a create a second column using =IF(A1<>"";cnum(A1);""). But the graph displays "" as 0 so I need to get rid of the "" values. Copy then Pastevalues in Excel doesn't do the job.

I often recreate on the fly a small VBA code to do that (For each cell in Range() IF cell.value ="" THEN cell.ClearContents...). That works great... but using VBA seems like an overkill.

Sometimes, I'll just copy the range, paste it in a text editor, copy it and paste if back in Excel. That works great too... but I'm not very pleased by that technique either.

How would you do that in Excel without using VBA ?

Edit 1 : I forgot to mention I would also often use =IF(A1<>"";cnum(A1);"X") then Copy/PasteValues then Ctrl+F for "X" and replace it with nothing. (If there are text values, I'll put "XYXYX" instead to be sure)

Edit 2 : I should have mentionned I'm only looking for solutions which either are either 1 second quicker (which means no or little use of the mouse), or as quick but nicer !

Possible duplicate of [Return empty cell from formula in Excel](http://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel) — GSerg, Oct 01 '15 at 21:37
@GSerg : Thanks for pointing it out. The context is a bit differnt, since in the other post, the question was "Is there a way to do this **with** VB?" But still, I'll have a look since many proposed answers skipped VB anyway. — Tibo, Oct 01 '15 at 21:59
@rwilson : Thanks. The post pointed by GSerg also adress the NA() solution. ("#N/A will result in the graphing routine interpolating over the cell whereas a truely empty cell will be treated as a gap in the line.") — Tibo, Oct 01 '15 at 22:03

score 1 · Accepted Answer · answered Oct 01 '15 at 21:46

Assuming your values come in "A" Column, put following formula to the "B" column:

=IF(A1<>"", 1, "")

Then copy whole "A" Column and Paste Special over the "B" Column specifying "Values" and then "Multiply" as operation and check "Skip Blanks"

After that copy whole "B" Column and Paste Special it over the "C" column (assuming it is empty) specifying "Values" and then "Add" as operation and also check "Skip Blanks".

So you have numeric data in "C" column with properly empty cells where you have no values.

Thanks for a good question!

Sure it'll work, but I'm not sure it'll be quicker than the "_copy to text editor then back to excel_" trick (assuming text editor launch instantaneously). I'll test that tomorrow anyway. Thanks. — Tibo, Oct 01 '15 at 22:20

score 1 · Answer 2 · 2015-10-01T22:11:53.617

Select the entire column (but only one column at a time).
Run Data ► Data Tools ► Text-to-Columns.
Pick Fixed width and click Finish. No need to go through the other screens.
All cells containing zero-length strings will become truly blank cells¹.

This procedure can quickly convert text-that-look-like-numbers to true numbers as well.

The VBA equivalent can greatly speed up looping through every cell in a range and dealing with them individually.

Dim c As Long
With Range("A1").CurrentRegion  `<~~ set to the desired range of one or more columns
    For c = 1 To .Columns.Count
        .Columns(c).TextToColumns Destination:=.Columns(c), _
            DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
    Next c
End With

¹_{This procedure is not applicable to cells with formulas returning zero-length strings. Those must be converted to their returned values with Copy, Paste Special, Values before running through Text-to-Columns.}

I didn't think of this easy trick, which is interesting for the included CNUM conversion. I'll test that. Thanks — Tibo, Oct 01 '15 at 22:15

Clear cell contents if cell's value is "" without using VBA

2 Answers2