1

In Excel, I have two columns with formulas that produce either text or #N/A.

COLUMN_1    COLUMN_2
text1       #N/A
#N/A        text2
#N/A        #N/A
text3       text4

I have a third column, which I want to fill with the contents of column 1 and column 2.

I only want to grab the non-N/A content, however. I want content

  • from either one from column 1 or column 2
  • or leave it blank if both column 1 and column 2 are N/A
  • or if both column 1 and column 2 have a result, I'd like to grab them both separated by any character or a space

An example would be:

COLUMN_1    COLUMN_2    COLUMN_3
text1       #N/A        text1
#N/A        text2       text2
#N/A        #N/A    
text3       text4       text3,text4

I started writing a formula with IF(), where its first argument was OR(NOT(ISNA(A2)),NOT(ISNA(B2))), but I didn't really know what to put as the output if that condition was met. I'm not adamant about using IF(), however.

Thanks!

  • +1 Wow! A clear explanation and an example that illustrates it. :-) – Scott Sep 6 '13 at 19:05
2

Try using some nested IF():

=IF(ISNA(A2), IF(ISNA(B2),"",B2) , IF(ISNA(B2),A2,A2&","&B2) )

You have 4 total possibilities and all the four are there.

  1. If both are NA, return nothing
  2. If A2 is NA but not B2, put B2
  3. If B2 is NA but not A2, put A2
  4. If both are not NA, put both

Alternatively, you could maybe use:

=IF(ISNA(A2),"",A2)&IF(AND(NOT(ISNA(A2)),NOT(ISNA(B2))),",","")&IF(ISNA(B2),"",B2)

Which doesn't use nested IF, but is no doubt a bit more complicated =P

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.