如何连接多个列（如果不为空） [英] How to Concatenate multiple columns if not empty

问题描述

我想将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

这篇关于如何连接多个列（如果不为空）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！