CODEWITHSUNDEEP
excelexcel-formulatextjoin
user7758051
user7758051

Reputation: 364

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

Upvotes: 0

Views: 176

Answers (2)

user4039065
user4039065

Reputation:

With a finite number of possible strings to concatenate, you can also use SUBSTITUTE and TRIM if you do not have access to TEXTJOIN.

=SUBSTITUTE(TRIM(A2&" "&B2&" "&C2), " ", "-")

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

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)

Upvotes: 1

Related Questions