Concantenating non adjacent cells

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
 
B

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
 
P

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
 
M

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 ..
 
B

Bob Freeman

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 ..
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top