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" 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

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

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

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.

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.