27

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?

Improve this question

5 Answers 5

Reset to default
33

Try using the ISNA() function:

=IF(ISNA(AR6347),"","string in here")
Improve this answer
7
  • 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, 2012 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, 2012 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, 2012 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, 2012 at 14:51
  • ya did what you suggested and im still getting the same problem :/
    – newSpringer
    Sep 17, 2012 at 14:57
8

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

=IFERROR(A1;"")

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

Improve this answer
5

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.

Improve this answer
0
1

SIMPLEST METHOD

You can use this directly in the cell with the formula if you want to skip the intermediate cell steps

=IFNA(formula,"text/value if formula result is #N/A")

This will put the result of the formula in the cell (if the result is not #N/A) and will put the text string (or whatever value you put as the second argument) in the cell instead if the formula result is #N/A.

I use it with VLOOKUP and INDEX-MATCH all the time when I don't want the #N/A's to show. I replace what would be an #N/A result with a blank cell ("") or zero(0) or text ("text string") as needed.

Improve this answer
-1

I used something similar to determine if an item in A matched one in D and not display #N/A. Used for presentation purposes. =IF(IFERROR(MATCH(A4,$D$2:$D$11,0),0)>0,"text for TRUE","text for FALSE")

Improve this answer
1
  • 6
    This is a ridiculously convoluted response when kurp's answer already describes the relevant way to use IFERROR.
    – fixer1234
    Oct 22, 2015 at 0:18

You must log in to answer this question.