Microosoft Excel 2007 How to Convert a Column with some #NA values to numerics
Posted on 2011-02-20
I have the following annoying problem in Excel 2007.
I have a column of data that has some valid numeric values but where the value is missing for that particular row the cell is a text value "#NA". I want to create a column that is a copy of this column except that I want all the "#NA" text values to be numeric zeroes.
I want to write a complex function call that will detect if the value in the cell is #NA and if so convert the value in the new cell to zero, otherwise leave the numeric value unchanged. This looks like it should be easy but its a torture process. Thanks Bill.
I have tried all sorts of variations using the IF function and the EXACT function but to no avail. Can anyone help.