串联不确定数目的单元格，而忽略Excel中的空白 [英] Concatenating indeterminate number of cells ignoring blanks in Excel

问题描述

请看本页上的示例

解决方案

如果您拥有Office 365 Excel，则可以使用TEXTJOIN():

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

---

否则，您需要添加到公式中以使用LEFT()删除最后一个字符

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

此公式有点不同，因为它总是将-添加到末尾.然后，它用Left删除最后一个-.然后，无论是否为空白都无所谓，我们总是在需要删除的末尾总是-.

---

或如@Jeeped所述:

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

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.

解决方案

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)

这篇关于串联不确定数目的单元格，而忽略Excel中的空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！