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 !

Answer 1

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!