CODEWITHSUNDEEP
excelvbaexcel-formula
ScotCal
ScotCal

Reputation: 55

Concatenate cells within Excel row based off variable start and end columns

I have a dataset which looks like the following:

A B C D E
1 4 5
2 3 6
1 3

I need to create a column which concatenates the cells in each row from the first cell with a value, to the last cell with a value, even if there are blanks in the middle. These need to be separated by a semi-colon. So the output for the first row would be 1; ;4;5. Second row would be 2;3; ;6. Third row would be 1; ; ; ;3.

As it stands I have managed to add a couple of formulas which identify the start and end column references within the range for each row (i.e. first row starts column 1, ends column 4).

The formula for finding first non-blank cell ref in row:

={MATCH(FALSE(ISBLANK(H6:AB6),0)}

The formula for finding last non-blank cell ref in row:

=AGGREGATE(14,6,(COLUMN(H6:AB6)-COLUMN(H6+1)/(H6:AB6<>""),1)

I am struggling with how to get the formula to use the starting column number for that row and pull back subsequent cell values with the ; separator until it gets to and includes the last column reference number.

Upvotes: 2

Views: 266

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34390

Just to answer the part of your question that asks about how to use the column number of first and last non-blank cell to get the result without leading and trailing semicolons, your formula would look like this:

=TEXTJOIN(";",0,INDEX(1:1,MATCH(FALSE,ISBLANK(A1:E1),0)):INDEX(1:1,AGGREGATE(14,6,(COLUMN(A1:E1)-COLUMN(A1)+1)/(A1:E1<>""),1)))

or shorter:

=TEXTJOIN(";",0,INDEX(1:1,MATCH(FALSE,ISBLANK(A1:E1),0)):INDEX(1:1,LOOKUP(2,1/(A1:E1<>""),COLUMN(A1:E1))))

The only advantage of using aggregate is that you don't need to array-enter it in earlier versions of Excel, but you don't need to array-enter the lookup formula either so I would tend to use that (and anyway in this case you probably need to array-enter the whole formula pre Excel-365).

This isn't too bad, but unfortunately if you wanted to make blanks into zeroes you would have to repeat most of the formula, unless you have Excel 365 and can use Let:

=TEXTJOIN(";",0,IF(INDEX(1:1,MATCH(FALSE,ISBLANK(A1:E1),0)):INDEX(1:1,LOOKUP(2,1/(A1:E1<>""),COLUMN(A1:E1)))="",0,
INDEX(1:1,MATCH(FALSE,ISBLANK(A1:E1),0)):INDEX(1:1,LOOKUP(2,1/(A1:E1<>""),COLUMN(A1:E1))) ))

enter image description here

If you have Excel 365, you can search from the end of the array using Xmatch:

=TEXTJOIN(";",0,INDEX(1:1,XMATCH(TRUE,A1:E1<>"")):INDEX(1:1,XMATCH(TRUE,A1:E1<>"",0,-1)))

Upvotes: 2

JvdV
JvdV

Reputation: 75990

A FILTERXML() alternative:

enter image description here

Formula in G1:

=TEXTJOIN(";",0,IFERROR(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",0,A1:E1)&"</s></t>","//s[.!='' or (following::*!='' and preceding::*!='')]"),""))

Upvotes: 5

Mian
Mian

Reputation: 161

Use in J1 or Any of your Required Cell

=IF(AND(A1="",B1="",C1="",D1="",E1=""),"",CONCATENATE(A1,IF(AND(B1="",C1="",D1="",E1=""),".",IF(A1="",""," ;")),B1,IF(AND(C1="",D1="",E1=""),IF(AND(B1="",C1="",D1="",E1=""),"","."),IF(AND(A1="",B1=""),""," ;")),C1,IF(AND(D1="",E1=""),IF(AND(C1="",D1="",E1=""),"","."),IF(AND(A1="",B1="",C1=""),""," ;")),D1,IF(E1="",IF(AND(D1="",E1=""),"","."),IF(AND(A1="",B1="",C1="",D1=""),""," ;")),E1,IF(E1="","",IF(E1="","","."))))

Hope Thats what you actually want.

Upvotes: 2

Related Questions