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 Wow! A clear explanation and an example that illustrates it. :-) – Scott Sep 6 '13 at 19:05

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