Concantenating non adjacent cells

  • Thread starter Thread starter Bob Freeman
  • Start date Start date
B

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
 
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
 
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
 
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 ..
 
Many thanks Max - saved me hours

Max said:
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 ..
Click to expand...
 
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
Click to expand...

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
 
Back
Top