Formula to concatenate two cell values only when both cells contain text

Dear all,

I tried to concatenate two cell values and intersperse the two cell values with a semicolon only when both cells contain text; in Excel 2007, using formulas only.

Interestingly, the following did NOT work:
=IF(ISBLANK(TRIM(E2)),IF(ISBLANK(TRIM(Q2)),"",Q2),IF(ISBLANK(TRIM(Q2)),E2,E2&";"&Q2))
as it apparently evaluates all the IF statements as FALSE, independent of the contents of the cells E2 and Q2.
Although it does not produce an error message,
t looks like one can NOT meaningfully combine ISBLANK and TRIM in this nested way,
nor can one meaningfully combine ISBLANK and CLEAN in a similarly nested way.

I therefore have two questions:
1. Is there is a formula to perform the conditional concatenation if cells contain blanks or other invisible characters ?
2. Why can one not meaningfully nest TRIM or CLEAN within ISBLANK ?

Try it like this:

=IF(TRIM(E2&Q2)="","",IF(TRIM(E2)="",Q2,IF(TRIM(Q2)="",E2,E2&";"&Q2)))

Hope this helps.

Pete

That helps a lot, Pete !
Thanks for your excellent solution !

I'm still curious as to why combining the ISBLANK and TRIM / CLEAN functions doesn't work.