2

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.

ZygD
  • 22,092
  • 39
  • 79
  • 102
ScotCal
  • 65
  • 1
  • 5
  • `{=MID((A1 & ";" &B1 & ";" & C1 & ";" &D1 & ";" & E1),MATCH(0,--ISBLANK(A1:E1),0),LEN(A1 & ";" &B1 & ";" & C1 & ";" &D1 & ";" & E1)-MATCH(0,--ISBLANK(A1:E1),0)+1)}` – Nathan_Sav Aug 16 '21 at 10:02

3 Answers3

5

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::*!='')]"),""))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you so much for this, hugely appreciated. May I ask if it possible for this to formula to return ‘0’ where the blank cells are? So instead of being 10;;5 it would be 10;0;5? I will read up on these formulas which are new to me and massively helpful. – ScotCal Aug 16 '21 at 10:21
  • 1
    @ScotCal, yes, change the value after the `IFERROR()` from the double quotes `""` to `"0"` . Btw, `FILTERXML()` has been a lifesaver for me in many occasions. If you wish, you can read more about it on another SO-thread I made [here](https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml) – JvdV Aug 16 '21 at 10:26
  • Maybe you could just use text join if you wanted zeroes for the blank cells =TEXTJOIN(";",1,IF(A1:E1="",0,A1:E1)) – Tom Sharpe Aug 16 '21 at 11:29
  • @TomSharpe, I guess that could be the alternative to `IFERROR()` =). I'd personally still use the latter to avoid false positives (imagine an empty row). – JvdV Aug 16 '21 at 11:33
  • Yes, my suggestion would give 1;0;4;5;0 for the first row anyway, whereas yours would give 1;0;4;5 which is probably what OP wants. – Tom Sharpe Aug 16 '21 at 11:36
2

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.

Mian
  • 137
  • 1
  • 12
2

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)))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37