Search code examples
excelexcel-formulatextjoin

Concatenating indeterminate number of cells ignoring blanks in Excel

Looking at the example on this page https://www.extendoffice.com/documents/excel/3269-excel-concatenate-cells-if-not-empty.html (shown below).

This solution doesn't work if the last entry is blank. For example, in column A if "Order" was missing from A5, the result would be "KTE-2015-Ruby-" instead of "KTE-2015-Ruby".

How would I go about obtaining the second result without a bunch of if statements checking if that cell is the last one that isn't blank.

For more information, I will always have to check the same number of rows for information, but the amount of rows that actually has text is indeterminate. Using the picture as an example, again, I will always only be checking rows 1-5, but I won't know which of those rows are blank.

enter image description here

Solution

  • If you have Office 365 Excel then you can use TEXTJOIN():

    =TEXTJOIN("-",TRUE,A1:A5)

    If not then you need to add to the formula to remove the last character using LEFT()

    =LEFT(A1 & "-" & IF(A2<>"",A2 & "-","") & IF(A3<>"",A3 & "-","") & IF(A4<>"",A4 & "-","") & IF(A5<>"",A5 & "-",""),LEN(A1 & "-" & IF(A2<>"",A2 & "-","") & IF(A3<>"",A3 & "-","") & IF(A4<>"",A4 & "-","") & IF(A5<>"",A5 & "-",""))-1)

    This formula is a little different as it always adds the - to the end. it then removes the last - with Left. Then it does not matter if any are blank or not we will always have the - at the end that needs to be removed.

    Or as @Jeeped stated:

    =MID(IF(A1<>"","-" & A1,"") & IF(A2<>"","-" & A2,"") & IF(A3<>"","-" & A3,"") & IF(A4<>"","-" & A4,"") & IF(A5<>"","-" & A5,""),2, 999999)