Concatenating non adjacent cells

[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

 

[מיכ×ל (מיקי) ×בידן]

http://www.microsoft.com/office/com...dcb592-3341-44ad-b2cf-28607adcf2c1&sloc=en-us

 

[Ms-Exl-Learner]

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),"*",TRIM(C2),"*",TRIM(D2),"*",TRIM(E2)),"BLANK*",""),"*BLANK","")

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+G>>SPECIAL>>select 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.

That’s It!!!

Remember to Click Yes, if this post helps!