-1

I am trying to concatenate the text of multiple cells into one cells where there is blank cells too, for blank cells it is taking '0' which i want to be blank eg.,

  • Cell 1 : Blank
  • Cell 2 : Blank
  • Cell 3 : A
  • Cell 4 : B
  • Cell 5 : Blank
  • Cell 6 : C
  • Cell 7 : Blank
  • Cell 8 : D

answer should be ABCD without any zeros.

James Z
  • 12,209
  • 10
  • 24
  • 44
Malini V
  • 1
  • 1
  • 3
    What is the formula you are using? If using `TEXTJOIN`. there's an argument to ignore empty cells. If your version of Excel doesn't support `TEXTJOIN`, there's a VBA equivalent [here](https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell). – BigBen Jul 13 '21 at 18:18
  • 1
    If you can't use `TEXTJOIN`, `=A1&A2&A3&A4&A5&A6&A7&A8` may work for you – cybernetic.nomad Jul 13 '21 at 19:13

2 Answers2

0

You can use this approach if you have Excel 365:

=TEXTJOIN("",TRUE,A1:H1)

OR

{=TEXTJOIN("",TRUE,IF(ISTEXT(A1:H1),A1:H1,""))}

If do not have Excel 365 then:

= CONCATENATE(
IF(COUNTBLANK(B1)=1,"",B1&CHAR(10)&","),
IF(COUNTBLANK(C1)=1,"",C1&CHAR(10)&","),
IF(COUNTBLANK(D1)=1,"",D1&CHAR(10)&","),
IF(COUNTBLANK(E1)=1,"",E1&CHAR(10)&","),
IF(COUNTBLANK(F1)=1,"",F1&CHAR(10)&","),
IF(COUNTBLANK(G1)=1,"",G1&CHAR(10)&","),
IF(COUNTBLANK(H1)=1,"",H1&CHAR(10)&","),
IF(COUNTBLANK(I1)=1,"",I1&CHAR(10)&","),
IF(COUNTBLANK(J1)=1,"",J1&CHAR(10))
)

enter image description here

Rajput
  • 605
  • 3
  • 12
0

With data in A1 through A8, pick a cell and enter:

=A1 & A2 & A3 & A4 & A5 & A6 & A7 & A8

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99