Concatenating When Some Cells Are Empty
Problem:
It's easy to concatenate columns and add a delimiter when all cells have data:
=A2&"; "&B2&"; "&C2
But if some cells are empty, you'll get unwanted delimiters:
Solution
Only insert a delimiter and concatenate a cell and if it has data (if its length is greater than 0)
=IF(LEN(A2)>0,A2,"")&IF(LEN(B2)>0,"; "&B2,"")&IF(LEN(C2)>0,"; "&C2,"")
The only problem is, if the first column cell is empty, you get a leading delimiter:
To get rid of the a leading delimiter (if the first value from the left is ";", remove the first two characters from the left)
=IF(LEFT(D2)=";",RIGHT(D2,LEN(D2)-2), D2)
or
=SUBSTITUTE(TRIM(SUBSTITUTE(D2,"; "," "))," ","; ")