MCONCAT
 

I am using this formula:

mconcat(A1:A100,";")

A1:100 has a formaula in it. In some cases the cell contains #N/A. The
mconcat function does not work in these cases. Is there a way to modify my
formala so that it works even if there is #N/A in the A1;A100 ?

Thank you in advance.

Hi
why not prevent the #NA error in the source cells with something like
=IF(ISNA(your_formula),"",your_formula)

--
Regards
Frank Kabel
Frankfurt, Germany
"carl" schrieb im Newsbeitrag
...
I am using this formula:

mconcat(A1:A100,";")

A1:100 has a formaula in it. In some cases the cell contains #N/A. The
mconcat function does not work in these cases. Is there a way to modify my
formala so that it works even if there is #N/A in the A1;A100 ?

Thank you in advance.

I would personally go with Frank's solution and fix the formulas that return
the errors,

=MCONCAT(IF(ISNA(A1:A100),"",A1:A100,";")

entered with ctrl + shift & enter will work



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"carl" wrote in message
...
I am using this formula:

mconcat(A1:A100,";")

A1:100 has a formaula in it. In some cases the cell contains #N/A. The
mconcat function does not work in these cases. Is there a way to modify my
formala so that it works even if there is #N/A in the A1;A100 ?

Thank you in advance.

"carl" wrote...
I am using this formula:

mconcat(A1:A100,";")

A1:100 has a formaula in it. In some cases the cell contains #N/A. The
mconcat function does not work in these cases. Is there a way to modify my
formala so that it works even if there is #N/A in the A1;A100 ?

Do you want empty fields corresponding to #N/As or skip those fields
entirely? If the former, use Frank or Peo's suggestions. If the latter, use
the array formula

=SUBSTITUTE(TRIM(MCONCAT(IF(ISNA(A1:A100),"",A1:A1 00)," "))," ",";")