Concatenating help

[mahmed1]

Hi guys,

I have this formula to concatenate

=a1&", "&a2&" and" &a3

now what i need to have is if cell a3 is empty
change the concatenation to
a1 and a2.
if a2 and a3 are empty then display
A1.

Hope this makes sence

thanks

 

[cgcamal]

Hi mahmed1,


An option could be:

Cell Formulas
Range	Formula
B1	=IF(AND(A1<>"",A2<>"",A3<>""),A1&" "&A2&" "&A3,IF(AND(A1<>"",A2<>""),A1&" "&A2,IF(AND(A1<>""),A1,"")))

Hope this helps,

Regards

 

[Blade Hunter]

=IF(COUNTA(A1:A3)=3,A1&", "&A2&" and "&A3,IF(COUNTA(A1:A2) = 2, A1 & " and " & A2,A1))

 

[mahmed1]

Thank you guys

 

[cgcamal]

You're welcome mahmed1.

Best regards

 

[needhelp2]

here is solution dear.
=IF(COUNTA(A1:A3)=3,A1&", "&A2&" and "&A3,IF(COUNTA(A1:A2) = 2, A1 & " and " & A2,A1))

 

[Jonmo1]

If the "and" is not essential.... a simple comma space between each value would suffice, then try

=SUBSTITUTE(TRIM(A1&" "&A2&" "&A3)," ",", ")