UC San Diego SearchMenu

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:

Excel: concat 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:

Excel: 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,"; "," "))," ","; ")

Excel: if the first value from the left is