问题描述
我想将9列的值连接成1列价值之间。问题是某些列的某些列为空,使得使用= CONCATENATE()函数非常丑,因为您需要检查= if(A2 =; ...) 9列。
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.
有没有更聪明的方法来组合Excel中的这些多列,只使用具有值的单元格?可能使用VBA?
Is there a smarter way to combine these multiple columns in excel, only using the cell that have values in it? Maybe using 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 | |
1.行的输出应为:lion | king | animals | dog,为2.线:狮子|女王|猫|喷气
Output for the 1. line should be: "lion|king|animals|dog" and for the 2. line: "lion|queen|cat|jet"
有人可以帮助吗?
推荐答案
您可以使用简单的UDF:
You could use a simple 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)
没有使用工作表公式执行此操作,而不使用凌乱的SUBSTITUTE / IF函数。
There isn't really anyway of doing this with a worksheet formula without using messy SUBSTITUTE/IF functions.
编辑(OP请求)
删除重复项:
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
这篇关于如何连接多个列(如果不为空)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!