Ignore #N/A while concatinate

[shivspatil]

Hi Gurus,
How do I uncount or igonre #N/A values while I concatenate say from cells A1, B1, C1, D1.. where B1 & D1 has #N/A values. I just want to concatenate values without #NA.

A1 - #N/A - C1 - #N/A

If I apply = concatenate(A1,B1,C1,D1) I expect - A1C1

Thanks a bunch

[alansidman]

Try this: =IF(OR(ISNA(B1),ISNA(D1)),CONCATENATE(A1,C1),CONCATENATE(A1,B1,C1,D1))

[shivspatil]

Hey thanks Alansidman, it works only when I have few entries, but I have at least 500 entries and I cant specifiy by each cell. I need formula to evaluate each cell and exculde it from concatenation if it is #N/A.


Thanks

[sourabhg98]

Try using a UDF (not created by me)
Check attached.
Credits-- http://www.contextures.com/rickroths...elvbatext.html

[Special-K]

You should really prevent the N/A's occurring beforehand