Convert from string to int in Excel for empty or blank

Question

As output I have for Range("H" & temp).Cells :

234
0
   (Empty)
2

I want to convert it into long or int, because it's a text value. So I did

Range("H" & temp).Cells = CInt(Range("H" & temp).Cells)

It works perfectly for 234, 0 and 2 but when the cell is empty it shows me error. What should I do? I want the empty cell to be taken as 0, of course using VBA macro.

Solution 1

That cell isn't really empty. It has a space or some other non-printing character in it. Try using Trim before using CInt to get rid of the spaces.

Solution 2

If you need to do this in formula, you can use this: IF(IFERROR(VALUE(A1)); 0; A1)