vba 如果不为空，如何连接多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27689666/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Concatenate multiple columns if not empty
提问by user1507035
I want to concatenate the values of 9 columns into 1 column with | between the values. The problem is that some of the columns are empty for some of the rows, making it pretty ugly to use the =CONCATENATE() function as you would need to check =if(A2="";...) for every of the 9 columns.
我想用 | 将 9 列的值连接成 1 列 值之间。问题是某些行的某些列是空的，因此使用 =CONCATENATE() 函数非常难看，因为您需要检查 =if(A2="";...) 9 列。
Is there a smarter way to combine these multiple columns in excel, only using the cell that have values in it? Maybe using VBA?
有没有更聪明的方法来在 excel 中组合这些多列，只使用其中有值的单元格？也许使用VBA？
To exemplify, the sheet looks something like:
举例来说，工作表看起来像：
| A | B | C | D | E | F | G | H | I |
|------+------+---+-------+---------+---+-----+-----+-----|
| lion | king | | | animals | | | | dog |
| lion | | | queen | | | cat | jet | |
Output for the 1. line should be: "lion|king|animals|dog" and for the 2. line: "lion|queen|cat|jet"
1. 行的输出应为：“lion|king|animals|dog”，2. 行的输出应为：“lion|queen|cat|jet”
Can someone help?
有人可以帮忙吗？
Thanks a lot upfront!!
非常感谢前面！
回答by Sam
You could use a simple UDF:
您可以使用一个简单的 UDF：
Function MyConcat(ConcatArea As Range) As String
For Each x In ConcatArea: xx = IIf(x = "", xx & "", xx & x & "|"): Next
MyConcat = Left(xx, Len(xx) - 1)
End Function
Copy the above code into a standard code module, and use in your worksheet like so:
将上述代码复制到标准代码模块中，并在您的工作表中使用，如下所示：
=MyConcat(A1:J1)
=MyConcat(A1:J1)
There isn't really anyway of doing this with a worksheet formula without using messy SUBSTITUTE/IF functions.
在不使用凌乱的 SUBSTITUTE/IF 函数的情况下，真的没有办法用工作表公式来做到这一点。
EDIT(OP request)
编辑（OP 请求）
To remove duplicates:
要删除重复项：
Function MyConcat(ConcatArea As Range) As String
For Each x In ConcatArea: xx = IIf(x = "" Or InStr(1, xx, x & "|") > 0, xx & "", xx & x & "|"): Next
MyConcat = Left(xx, Len(xx) - 1)
End Function
回答by dee
Public Function ConcatItNoDuplicities(ByVal cellsToConcat As Range) As String
ConcatItNoDuplicities = ""
If cellsToConcat Is Nothing Then Exit Function
Dim oneCell As Range
Dim result As String
For Each oneCell In cellsToConcat.Cells
Dim cellValue As String
cellValue = Trim(oneCell.value)
If cellValue <> "" Then
If InStr(1, result, cellValue, vbTextCompare) = 0 Then _
result = result & cellValue & "|"
End If
Next oneCell
If Len(result) > 0 Then _
result = Left(result, Len(result) - 1)
ConcatItNoDuplicities = result
End Function
回答by xificurC
You could use a UDF like this (adjust to own needs):
您可以使用这样的 UDF（根据自己的需要进行调整）：
Function Conc(v As Variant, Optional ByVal sDelim As String = "") As String
Dim vLoop As Variant
If IsArray(v) Or TypeName(v) = "Range" Then
For Each vLoop In v
If Conc = "" Then
Conc = vLoop
ElseIf vLoop <> "" Then
Conc = Conc & sDelim & vLoop
End If
Next vLoop
Else
Conc = CStr(v)
End If
End Function
