Concatenating 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

Concatenating non adjacent cells
 

http://www.microsoft.com/office/comm...2c1&sloc=en-us


"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

Concatenating non adjacent cells
 

Assume that you are having the example database from A1 to E4 range like the
below:-

COL A COL B COL C COL D COL E
ROW1 ID 1 2 3 4
ROW2 Z A BLANK C D
ROW3 Y BLANK B C BLANK
ROW4 X A B BLANK D

In cell F2 paste the below formula:

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(TRIM(B2),"*",TR IM(C2),"*",TRIM(D2),"*",TRIM(E2)),"BLANK*",""),"*B LANK","")

Copy the F2 cell and paste it for the remaining cells of F Column.

Now the results will show like the below:-
A**C*D
*B*C*
A*B**D

Now place the cursor in B1 and select upto E4 that is the selection range
should be B1:E4. Press CNTRL+GSPECIALselect BLANKS Option Button and
press ok. Now you will see the blank cells will be getting selected (i.e.)
B3,C2, D4 & E3. Now press Cntrl+H which will open the find and replace
dialog box. In FIND WHAT: field leave it as blank and in REPLACE WITH: field
type BLANK and give REPLACE ALL and click close.

Now you can notice that the cell F2 to F4 will show the desired results.
Copy and paste the F Column formula to values.

Now select B1:E4 again press Cntrl+H which will open the find and replace
dialog box. In FIND WHAT: type BLANK and in REPLACE WITH: field leave it as
blank and give REPLACE ALL and click close, which will restore the cell which
is having the character BLANK from B1:E4 to blank cells.

Thats It!!!

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"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