Concantenating non adjacent cells

[Bob Freeman]

Hello - I am trying to create a field that concatenates cells that are
populated from the previous 12 cells on that row, but excluding blanks and
adding a * delimited character between each instance. Please find a 4 column
example below

ID 1 2 3 4 Result required
Z A C D A*C*D
Y B C B*C
X A B D A*B*D

I will be applying this to a 2007 version spreadsheet containing in excess
of 10,000 lines. There will be at least 5 blank cells on each row.

Many thanks - Bob

 

[מיכ×ל (מיקי) ×בידן]

The answer/suggestion/solution will remain the same as in ALL your previous
posts.
Micky

 

[Bob Freeman]

Hello,

Apologies for this - My browser has not been updating so I have not been
able to see any responses - will now check

Apologies again

 

[Per Jessen]

Hi Bob

This UDF will do the trick!

Public Function MyConcantenate(ByRef InputCells As Range) As String
Dim Temp As String
For Each cell In InputCells.Cells
If cell.Value <> "" Then
Temp = Temp & cell.Value
End If
Next
MyConcantenate = Temp
End Function

Regards,
Per

 

[Max]

One way ..
Assume your data to be concat in cols B to E, from row 2 down
In F2: =SUBSTITUTE(TRIM(B2&" "&C2&" "&D2&" "&E2)," ","*")
Copy down as far as required. voila? immortalize this response, hit YES
below ..

 

[Bob Freeman]

Many thanks Max - saved me hours

One way ..
Assume your data to be concat in cols B to E, from row 2 down
In F2: =SUBSTITUTE(TRIM(B2&" "&C2&" "&D2&" "&E2)," ","*")
Copy down as far as required. voila? immortalize this response, hit YES
below ..

 

[Ron Rosenfeld]

Hello - I am trying to create a field that concatenates cells that are
populated from the previous 12 cells on that row, but excluding blanks and
adding a * delimited character between each instance. Please find a 4 column
example below

ID 1 2 3 4 Result required
Z A C D A*C*D
Y B C B*C
X A B D A*B*D

I will be applying this to a 2007 version spreadsheet containing in excess
of 10,000 lines. There will be at least 5 blank cells on each row.

Many thanks - Bob

You could use a User Defined Function:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConcatRg(A1:L12)

in some cell.

=============================================
Option Explicit
Function ConcatRg(rg As Range, Optional Separator As String = "*") As String
Dim temp() As String
Dim c As Range
Dim i As Long

ReDim temp(0 To WorksheetFunction.CountA(rg) - 1)
For Each c In rg
If c <> "" Then
temp(i) = c.Text
i = i + 1
End If
Next c

ConcatRg = Join(temp, Separator)

End Function
=======================================
--ron