Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have over 6000 records and half of them are formulas that are missing a variable so they result in #N/A on the spreadsheet, what i want to do is if the cell is #N/A then leave the cell blank, otherwise print a string like so

=IF(AR6347="#N/A","","string in here")

But this does not work with ="#N/A", is there a way to do this?

share|improve this question

migrated from stackoverflow.com Sep 17 '12 at 17:23

This question came from our site for professional and enthusiast programmers.

3 Answers 3

Try using the ISNA() function:

=IF(ISNA(AR6347),"","string in here")
share|improve this answer
    
this will do it for individual cells but it will not let me copy this down the 6000 cells, gives out with this operation requires the merged cells to be identically sized, is there a way to do this so i can copy it for all cells? –  newSpringer Sep 17 '12 at 13:59
    
Sounds like some of the cells in your target column are merged. Assuming that you do need them to stay merged, follow the instructions from saruman576 midway down mrexcel.com/forum/excel-questions/74144-find-merged-cells.html (using the Format option to find merged cells). You could then copy the formula up until each merged cell and then handle the merged cell separately. However I would suggest looking into Center Across Selection if applicable (thenewpaperclip.com/2008/04/15/…). Hope this helps! –  RocketDonkey Sep 17 '12 at 14:10
    
i was looking into this and the cells are not merged (went into format cells) so this not this :/ –  newSpringer Sep 17 '12 at 14:45
    
Are there no merged cells anywhere in that entire column? What it sounds like it happening is when you are trying to copy the formula down, it is running into a merge cell, which expands the copy range column-wise to include that entire cell (which will also pick up merged cells in the second column and then expand them, etc.). If you don't require any merged cells, try selecting everything and then unmerge the cells (in Excel 2007, Home -> Alignment -> Unmerge All Cells [in the Merge And Center drop-down]). –  RocketDonkey Sep 17 '12 at 14:51
    
ya did what you suggested and im still getting the same problem :/ –  newSpringer Sep 17 '12 at 14:57

Use the iserror() function. For instance, with a vlookup not finding a value in my table, I want to display Not found instead of #N/A, then I type the following:

=if(iserror(vlookup(A1,Sheet2!$A$1:$C$360,3,0)),'Not found',vlookup(A1,Sheet2!$A$1:$C$360,3,0))

So, this formula is just saying: if the vlookup function is retrieving an error, then return the string 'Not found', else return the result of the vlookup function.

share|improve this answer

In Excel 2007 and later you're able to use:

=IFERROR(A1;"")

to replace ="#N/A" or any other error with empty string.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.