Excel: grab the non-N/A contents from columns

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 Answer

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