Concantenating non adjacent cells
 

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

Concantenating non adjacent cells
 

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


"Bob Freeman" wrote:

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

Concantenating non adjacent cells
 

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

"מיכאל (מיקי) אבידן" wrote:

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


"Bob Freeman" wrote:

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

Concantenating non adjacent cells
 

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

"Bob Freeman" skrev i meddelelsen
...
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

Concantenating non adjacent cells
 

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 ..
--
Max
Singapore
---
"Bob Freeman" wrote:
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.

Concantenating non adjacent cells
 

Many thanks Max - saved me hours

"Max" wrote:

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 ..
--
Max
Singapore
---
"Bob Freeman" wrote:
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.