Combining Fields without getting extra spaces or semi-colons

[SpeakingPeace]

I'm trying to combine text data from 8 fields into one and separate them with a semi colon and a space. This formula works well....

=K2&"; "&L2&"; "&M2&"; "&N2&"; "&O2&"; "&P2&"; "&Q2&"; "&R2

....except when there are blank fields it still enters "; ". In the extreme example, if all the fields are blank I end up getting ";;;;;;;;". Ideally, I don't want these extra semi colons or spaces if there is a blank field.

Any thoughts?

[FDibbins]

maybe this brute-force version...
=IF(K2="","",K2&"; ")&IF(L2="","",L2&"; ")&IF(M2="","",M2&"; ")&IF(N2="","",N2&"; ")&IF(O2="","",O2&"; ")&IF(P2="","",P2&"; ")&IF(Q2="","",Q2&"; ")&R2

[SpeakingPeace]

FDibbins - Wow! Maybe "brute-force" to you but a think of beauty to me. Thank you!

[SpeakingPeace]

BTW, is there a way to do this without adding a semi colon or space after the last value? As an example, in FDibbins version....

=IF(K2="","",K2&"; ")&IF(L2="","",L2&"; ")&IF(M2="","",M2&"; ")&IF(N2="","",N2&"; ")&IF(O2="","",O2&"; ")&IF(P2="","",P2&"; ")&IF(Q2="","",Q2&"; ")&R2

..... if "Ohio" was in one of the eight fields and the other seven fields were blank, it would return "Ohio; ". Just wondering how it could be done without having a "; " at the end of the last value.

[Ace_XL]

Try..

=SUBSTITUTE(TRIM(K2&" "&L2&" "&M2&" "&N2&" "&O2&" "&P2&" "&Q2&" "&R2)," ","; ")

[FDibbins]

Or this slight variation (told you it was brute-force lol)....
=K2&IF(K2="","","; ")&L2&IF(L2="","","; ")&M2&IF(M2="","","; ")&N2&IF(N2="","","; ")&O2&IF(O2="","","; ")&P2&IF(P2="","","; ")&Q2&IF(Q2="","","; ")&R2

[SpeakingPeace]

Ace_XL - Sweet! Thanks!

[oeldere]

If it is allowed with an helpcolumn you can solve it like in the attached file.