Concatenating non adjacent cells

[Bob Freeman]

Hello, I am trying to create a result field, concatenating populated cells
from the previous 12 columns on that line, but excluding blank cells and
putting a * delimiting character between each instance - please find below a
4 column example.
ID 1 2 3 4 Result
Z A C D A*C*D
Y B C B*C
X A B D A*B*D
Each of the 10,000 lines of the spreadsheet is different - there are at
least 5 blank cells on each line. Any help gratefully received. Many thanks.
Bob

 

[Jacob Skaria]

Try the formula

=MID(IF(B2="","","*" &B2)&IF(C2="","","*" &C2)
&IF(D2="","","*" &D2)&IF(E2="","","*" &E2),2,99)

OR try this UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

Syntax:
=CONCATRANGE(rngRange,strDelimiter,blnIgnoreBlank)
rngRange is the Range
strDelimiter Optional . Default is space
blnIgnoreBlank Optional. Default is False

Examples:
'1. Concatenate with default delimiter(space)
=CONCATRANGE(A1:A10)

'2. Concatenate with semicolon as delimiter and ignore blanks
=CONCATRANGE(A1:A10,":",1)

Function CONCATRANGE(rngRange As Range, _
Optional strDelimiter As String = " ", _
Optional blnIgnoreBlank As Boolean = False)
Dim varTemp As Range
For Each varTemp In rngRange
If blnIgnoreBlank Then
If Trim(varTemp) <> vbNullString Then _
CONCATRANGE = CONCATRANGE & strDelimiter & varTemp
Else
CONCATRANGE = CONCATRANGE & strDelimiter & varTemp
End If
Next
CONCATRANGE = WorksheetFunction.Trim(Mid(CONCATRANGE, _
len(strDelimiter)+1))
End Function

 

[JLatham]

Another option, assumes columns containing text to concatenate are A through
L, change accordingly.
=LEFT(IF(A1="","",A1 & "*") & IF(B1="","",B1 & "*") & IF(C1="","",C1 & "*")
& IF(D1="","",D1 & "*") & IF(E1="","",E1 & "*") & IF(F1="","",F1 & "*") &
IF(G1="","",G1 & "*") & IF(H1="","",H1 & "*") & IF(I1="","",I1 & "*") &
IF(J1="","",J1 & "*") & IF(K1="","",K1 & "*") & IF(L1="","",L1 &
"*"),LEN(IF(A1="","",A1 & "*") & IF(B1="","",B1 & "*") & IF(C1="","",C1 &
"*") & IF(D1="","",D1 & "*") & IF(E1="","",E1 & "*") & IF(F1="","",F1 & "*")
& IF(G1="","",G1 & "*") & IF(H1="","",H1 & "*") & IF(I1="","",I1 & "*") &
IF(J1="","",J1 & "*") & IF(K1="","",K1 & "*") & IF(L1="","",L1 & "*"))-1)

Will error if all 12 cells are empty. This modification will prevent the
#VALUE! error from appearing when all 12 are empty
=IF(LEN(IF(A1="","",A1&"*")&IF(B1="","",B1&"*")&IF(C1="","",C1&"*")&IF(D1="","",D1&"*")&IF(E1="","",E1&"*")&IF(F1="","",F1&"*")&IF(G1="","",G1&"*")&IF(H1="","",H1&"*")&IF(I1="","",I1&"*")&IF(J1="","",J1&"*")&IF(K1="","",K1&"*")&IF(L1="","",L1&"*"))=0,"",LEFT(IF(A1="","",A1&"*")&IF(B1="","",B1&"*")&IF(C1="","",C1&"*")&IF(D1="","",D1&"*")&IF(E1="","",E1&"*")&IF(F1="","",F1&"*")&IF(G1="","",G1&"*")&IF(H1="","",H1&"*")&IF(I1="","",I1&"*")&IF(J1="","",J1&"*")&IF(K1="","",K1&"*")&IF(L1="","",L1&"*"),LEN(IF(A1="","",A1&"*")&IF(B1="","",B1&"*")&IF(C1="","",C1&"*")&IF(D1="","",D1&"*")&IF(E1="","",E1&"*")&IF(F1="","",F1&"*")&IF(G1="","",G1&"*")&IF(H1="","",H1&"*")&IF(I1="","",I1&"*")&IF(J1="","",J1&"*")&IF(K1="","",K1&"*")&IF(L1="","",L1&"*"))-1))