Search code examples
excel-formulaconcatenationtrimsubstitution

Is there a way to remove empty line break from a concatenation result?

I'm concatenating cells from different rows into one cell, using line break (char(10)). Because cells with empty fields are being concatenated, this is adding empty lines in the process. Is there a way to modify my formula in order to remove those empty lines?

    =IF(COUNTBLANK(A2);"";CONCATENATE(E2; CHAR(10); D2; CHAR(10); C2; CHAR(10); F2; CHAR(10); G2; CHAR(10); H2; CHAR(10); I2))

results with empty lines

Solution

  • If your specific order that you use is not important and you have OFFICE 365 Excel then you can use TEXTJOIN:

    =IF(A2="","",TEXTJOIN(CHAR(10),TRUE,C2:I2)

    If it matters and you have OFFICE 365 then you can use TEXTJOIN as an array formula:

    =IF(A2="","",TEXTJOIN(CHAR(10),TRUE,CHOOSE({1,2,3,4,5,6,7},E2,D2,C2,F2,G2,H2,I2))

    Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

    If you do not have Office 365 then you will need to contatenate a string of IFs

    IF(E2<>"",CHAR(10) & E2,"") & IF(D2<>"",CHAR(10) & D2,"") & ...

    Wrap that in a mid to remove the first CHAR(10)

    MID(IF(E2<>"",CHAR(10) & E2,"") & IF(D2<>"",CHAR(10) & D2,"") & ...,2,999)