Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenating non adjacent cells
Hello, I am trying to create a result field, concatenating populated cells
from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line. Any help gratefully received. Many thanks. Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenating non adjacent cells
Try the formula
=MID(IF(B2="","","*" &B2)&IF(C2="","","*" &C2) &IF(D2="","","*" &D2)&IF(E2="","","*" &E2),2,99) OR try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Syntax: =CONCATRANGE(rngRange,strDelimiter,blnIgnoreBlank) rngRange is the Range strDelimiter Optional . Default is space blnIgnoreBlank Optional. Default is False Examples: '1. Concatenate with default delimiter(space) =CONCATRANGE(A1:A10) '2. Concatenate with semicolon as delimiter and ignore blanks =CONCATRANGE(A1:A10,":",1) Function CONCATRANGE(rngRange As Range, _ Optional strDelimiter As String = " ", _ Optional blnIgnoreBlank As Boolean = False) Dim varTemp As Range For Each varTemp In rngRange If blnIgnoreBlank Then If Trim(varTemp) < vbNullString Then _ CONCATRANGE = CONCATRANGE & strDelimiter & varTemp Else CONCATRANGE = CONCATRANGE & strDelimiter & varTemp End If Next CONCATRANGE = WorksheetFunction.Trim(Mid(CONCATRANGE, _ len(strDelimiter)+1)) End Function -- Jacob "Bob Freeman" wrote: Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line. Any help gratefully received. Many thanks. Bob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenating non adjacent cells
Another option, assumes columns containing text to concatenate are A through
L, change accordingly. =LEFT(IF(A1="","",A1 & "*") & IF(B1="","",B1 & "*") & IF(C1="","",C1 & "*") & IF(D1="","",D1 & "*") & IF(E1="","",E1 & "*") & IF(F1="","",F1 & "*") & IF(G1="","",G1 & "*") & IF(H1="","",H1 & "*") & IF(I1="","",I1 & "*") & IF(J1="","",J1 & "*") & IF(K1="","",K1 & "*") & IF(L1="","",L1 & "*"),LEN(IF(A1="","",A1 & "*") & IF(B1="","",B1 & "*") & IF(C1="","",C1 & "*") & IF(D1="","",D1 & "*") & IF(E1="","",E1 & "*") & IF(F1="","",F1 & "*") & IF(G1="","",G1 & "*") & IF(H1="","",H1 & "*") & IF(I1="","",I1 & "*") & IF(J1="","",J1 & "*") & IF(K1="","",K1 & "*") & IF(L1="","",L1 & "*"))-1) Will error if all 12 cells are empty. This modification will prevent the #VALUE! error from appearing when all 12 are empty: =IF(LEN(IF(A1="","",A1&"*")&IF(B1="","",B1&"*")&IF (C1="","",C1&"*")&IF(D1="","",D1&"*")&IF(E1="","", E1&"*")&IF(F1="","",F1&"*")&IF(G1="","",G1&"*")&IF (H1="","",H1&"*")&IF(I1="","",I1&"*")&IF(J1="","", J1&"*")&IF(K1="","",K1&"*")&IF(L1="","",L1&"*"))=0 ,"",LEFT(IF(A1="","",A1&"*")&IF(B1="","",B1&"*")&I F(C1="","",C1&"*")&IF(D1="","",D1&"*")&IF(E1="","" ,E1&"*")&IF(F1="","",F1&"*")&IF(G1="","",G1&"*")&I F(H1="","",H1&"*")&IF(I1="","",I1&"*")&IF(J1="","" ,J1&"*")&IF(K1="","",K1&"*")&IF(L1="","",L1&"*"),L EN(IF(A1="","",A1&"*")&IF(B1="","",B1&"*")&IF(C1=" ","",C1&"*")&IF(D1="","",D1&"*")&IF(E1="","",E1&"* ")&IF(F1="","",F1&"*")&IF(G1="","",G1&"*")&IF(H1=" ","",H1&"*")&IF(I1="","",I1&"*")&IF(J1="","",J1&"* ")&IF(K1="","",K1&"*")&IF(L1="","",L1&"*"))-1)) "Bob Freeman" wrote: Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line. Any help gratefully received. Many thanks. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add cells on two adjacent rows but non-adjacent columns | Excel Worksheet Functions | |||
Help with blank cells when concatenating | Excel Worksheet Functions | |||
Concatenating info from several cells into the one above | Excel Discussion (Misc queries) | |||
Concatenating Cells | Excel Discussion (Misc queries) | |||
concatenating cells with their formats | Excel Worksheet Functions |