用最后一个现有文本替换公式以添加“&”

=IFERROR(REPLACE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," "),MAX(IFERROR(FIND(",",SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," "),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," "))))),0)),1," &"),SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(C28," ","^")&" "&SUBSTITUTE(C29," ","^")&" "&SUBSTITUTE(C30," ","^"))," ", ", "),"^"," "))

嘴里有点饱,但这能做到…这是一个数组公式,用确认公式 Ctrl键 + 换档 + 进入 仍然在公式栏中。

Source

您是否尝试使用“连接”代替“&”&?

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(TRIM(SUBSTITUTE(C28," ","^"))," ",TRIM(SUBSTITUTE(C29," ","^"))," ",TRIM(SUBSTITUTE(C30," ","^")))," ",", "),"^"," ")

在XL2016/Office 365中试试这个,

=textjoin(", ", true, c28:c30)

如果您没有textjoin函数,可以在 TEXTJOIN for xl2013 with criteria 。

作为一种可以在大多数版本的Excel中工作的冗长方法,请尝试以下操作:

=IF(COUNTA(C28:C30)=3,TRIM(C28) & ", " & TRIM(C29) & ", " & TRIM(C29), IF(COUNTA(C28:C30)<2, TRIM(C28 & C29 & C30), IF(COUNTA(C28:C29)=2,TRIM(C28) & " & " & TRIM(C29), IF(COUNTA(C29:C30)=2,TRIM(C28) & " & " & TRIM(C29),TRIM(C28) & " & " & TRIM(C29)))))

工作原理

=IF(COUNTA(C28:C30)=3,TRIM(C28) & ", " & TRIM(C29) & ", " & TRIM(C29)
如果我们有3个项目,请将它们与 ", " 在他们之间

IF(COUNTA(C28:C30)<2, TRIM(C28 & C29 & C30)
对于0或1个项,将所有内容连接在一起以不显示任何内容或显示单个项

IF(COUNTA(C28:C29)=2,TRIM(C28) & " & " & TRIM(C29)
如果我们只有第一个和第二个项目,请将它们与 " & " 项目之间

IF(COUNTA(C29:C30)=2,TRIM(C28) & " & " & TRIM(C29),TRIM(C28) & " & " & TRIM(C29))
如果我们只有第二个和第三个项目,请将它们与 “&” 在两者之间
否则,用 “&” 在两者之间

)))
关闭我们所有的 IF S

简化和更通用的Excel-365 只有 版本:

=IF(COUNTA(C28:C30)=2, TEXTJOIN(" & ", true, c28:c30), TEXTJOIN(", ", true, c28:c30))

(如果只有两项,则跳过空白并将其与 “&” ,否则跳过空白并将其与 “,” . 它还处理任何长度的列表,而不仅仅是3个项目)