vba 如何将一列中的行合并到excel中的一个单元格中？

How to merge rows in a column into one cell in excel?

提问by red23jordan

E.g

例如

A1:I
A2:am
A3:a
A4:boy 

I want to merge them all to a single cell "Iamaboy"
This example shows 4 cells merge into 1 cell however I have many cells (more than 100), I can't type them one by one using A1 & A2 & A3 & A4what can I do?

我想将它们全部合并到一个单元格“Iamaboy”
此示例显示 4 个单元格合并为 1 个单元格，但是我有很多单元格（超过 100 个），我无法使用A1 & A2 & A3 & A4我能做些什么来一一输入它们？

回答by Mischinab

If you prefer to do this without VBA, you can try the following:

如果您更喜欢在没有 VBA 的情况下执行此操作，可以尝试以下操作：

1. Have your data in cells A1:A999 (or such)
2. Set cell B1 to "=A1"
3. Set cell B2 to "=B1&A2"
4. Copy cell B2 all the way down to B999 (e.g. by copying B2, selecting cells B3:B99 and pasting)

1. 将您的数据放在单元格 A1:A999（或此类）中
2. 将单元格 B1 设置为“=A1”
3. 将单元格 B2 设置为“=B1&A2”
4. 将单元格 B2 一直复制到 B999（例如通过复制 B2，选择单元格 B3:B99 并粘贴）

Cell B999 will now contain the concatenated text string you are looking for.

单元格 B999 现在将包含您要查找的串联文本字符串。

回答by aevanko

I present to you my ConcatenateRange VBA function (thanks Jean for the naming advice!) . It will take a range of cells (any dimension, any direction, etc.) and merge them together into a single string. As an optional third parameter, you can add a seperator (like a space, or commas sererated).

我向您展示了我的 ConcatenateRange VBA 函数（感谢 Jean 的命名建议！）。它将采用一系列单元格（任何维度、任何方向等）并将它们合并为一个字符串。作为可选的第三个参数，您可以添加一个分隔符（如空格或带锯齿的逗号）。

In this case, you'd write this to use it:

在这种情况下，您可以编写以下代码来使用它：

=ConcatenateRange(A1:A4)

Function ConcatenateRange(ByVal cell_range As range, _
                    Optional ByVal separator As String) As String

Dim newString As String
Dim cell As Variant

For Each cell in cell_range
    If Len(cell) <> 0 Then
        newString = newString & (separator & cell)
    End if
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(separator)))
End If

ConcatenateRange = newString

End Function

回答by KCD

Inside CONCATENATEyou can use TRANSPOSEif you expand it (F9) then remove the surrounding {}brackets like thisrecommends

在里面CONCATENATE你可以使用，TRANSPOSE如果你扩展它（F9）然后删除周围的{}括号，就像这样推荐

=CONCATENATE(TRANSPOSE(B2:B19))

Becomes

成为

=CONCATENATE("Oh ","combining ", "a " ...)

You may need to add your own separator on the end, say create a column C and transpose that column.

您可能需要在最后添加自己的分隔符，比如创建一个 C 列并转置该列。

=B1&" "
=B2&" "
=B3&" "

回答by y.selivonchyk

In simple cases you can use next method which doesn`t require you to create a function or to copy code to several cells:

在简单的情况下，您可以使用不需要创建函数或将代码复制到多个单元格的 next 方法：

1. In any cell write next code

   =Transpose(A1:A9) 
   

1. 在任何单元格中编写下一个代码

   =Transpose(A1:A9) 
   

Where A1:A9 are cells you would like to merge.

其中 A1:A9 是您要合并的单元格。

1. Without leaving the cell press F9

2. 无需离开细胞压力 F9

After that, the cell will contain the string:

之后，单元格将包含字符串：

={A1,A2,A3,A4,A5,A6,A7,A8,A9}

Source: http://www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-excel/

资料来源：http: //www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-excel/

Update: One part can be ambiguous. Without leaving the cell means having your cell in editor mode. Alternatevly you can press F9 while are in cell editor panel (normaly it can be found above the spreadsheet)

更新：一部分可能不明确。不离开单元格意味着让您的单元格处于编辑器模式。或者，您可以在单元格编辑器面板中按 F9（通常可以在电子表格上方找到）

回答by Jean-Fran?ois Corbett

Use VBA's already existing Joinfunction. VBA functions aren't exposed in Excel, so I wrap Joinin a user-defined function that exposes its functionality. The simplest form is:

使用 VBA 已经存在的Join功能。VBA 函数未在 Excel 中公开，因此我封装Join了一个公开其功能的用户定义函数。最简单的形式是：

Function JoinXL(arr As Variant, Optional delimiter As String = " ")
    'arr must be a one-dimensional array.
    JoinXL = Join(arr, delimiter)
End Function

Example usage:

用法示例：

=JoinXL(TRANSPOSE(A1:A4)," ") 

entered as an array formula (using Ctrl-Shift-Enter).

作为数组公式输入（使用Ctrl- Shift- Enter）。

---

---

Now, JoinXLaccepts only one-dimensional arrays as input. In Excel, ranges return two-dimensional arrays. In the above example, TRANSPOSEconverts the 4×1 two-dimensional array into a 4-element one-dimensional array (this is the documented behaviour of TRANSPOSEwhen it is fed with a single-column two-dimensional array).

现在，JoinXL只接受一维数组作为输入。在 Excel 中，范围返回二维数组。在上面的示例中，TRANSPOSE将 4×1 的二维数组转换为 4 元素的一维数组（这是TRANSPOSE使用单列二维数组馈送时记录的行为）。

For a horizontal range, you would have to do a double TRANSPOSE:

对于水平范围，您必须执行 double TRANSPOSE：

=JoinXL(TRANSPOSE(TRANSPOSE(A1:D1)))

The inner TRANSPOSEconverts the 1×4 two-dimensional array into a 4×1 two-dimensional array, which the outer TRANSPOSEthen converts into the expected 4-element one-dimensional array.

内部TRANSPOSE将 1×4 二维数组转换为 4×1 二维数组，TRANSPOSE然后外部将其转换为预期的 4 元素一维数组。

This usage of TRANSPOSEis a well-known way of converting 2D arrays into 1D arrays in Excel, but it looks terrible. A more elegant solution would be to hide this away in the JoinXLVBA function.

这种用法TRANSPOSE是Excel中将二维数组转换为一维数组的一种众所周知的方法，但看起来很糟糕。更优雅的解决方案是将其隐藏在JoinXLVBA 函数中。

回答by gvo

For those who have Excel 2016(and I suppose next versions), there is now directly the CONCATfunction, which will replace the CONCATENATEfunction.

对于那些拥有Excel 2016（我想是下一个版本）的人来说，现在直接有CONCAT函数，它将取代CONCATENATE函数。

So the correct way to do it in Excel 2016 is :

因此，在 Excel 2016 中执行此操作的正确方法是：

=CONCAT(A1:A4)

which will produce :

这将产生：

Iamaboy

我是一个男孩

For users of olders versions of Excel, the other answers are relevant.

对于旧版本 Excel 的用户，其他答案是相关的。

回答by Chris Riley

For Excel 2011 on Mac it's different. I did it as a three step process.

对于 Mac 上的 Excel 2011，情况有所不同。我把它作为一个三步过程。

1. Create a column of values in column A.
2. In column B, to the right of the first cell, create a rule that uses the concatenate function on the column value and ",". For example, assuming A1 is the first row, the formula for B1 is =B1. For the next row to row N, the formula is =Concatenate(",",A2). You end up with:

1. 在 A 列中创建一列值。
2. 在 B 列中，在第一个单元格的右侧，创建一个规则，该规则对列值和“,”使用连接函数。例如，假设 A1 是第一行，则 B1 的公式是=B1。对于第 N 行的下一行，公式为=Concatenate(",",A2)。你最终得到：

QA
,Sekuli
,Testing
,Applitools
,Visual Testing
,Test Automation
,Selenium

1. In column C create a formula that concatenates all previous values. Because it is additive you will get all at the end. The formula for cell C1 is =B1. For all other rows to N, the formula is =Concatenate(C1,B2). And you get:

3. 在 C 列中创建一个连接所有先前值的公式。因为它是可加的，你最终会得到所有。单元格 C1 的公式是=B1。对于所有其他行到 N，公式为=Concatenate(C1,B2)。你会得到：

QA,Sekuli
QA,Sekuli,Testing
QA,Sekuli,Testing,Applitools
QA,Sekuli,Testing,Applitools,Visual Testing
QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation
QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation,Selenium

The last cell of the list will be what you want. This is compatible with Excel on Windows or Mac.

列表的最后一个单元格将是您想要的。这与 Windows 或 Mac 上的 Excel 兼容。

回答by GoldPaintedLemons

I use the CONCATENATEmethod to take the values of a column and wrap quotes around them with columns in between in order to quickly populate the WHERE IN ()clause of a SQL statement.

我使用该CONCATENATE方法获取列的值并用引号将它们括起来，并在它们之间使用列，以便快速填充WHERE IN ()SQL 语句的子句。

I always just type =CONCATENATE("'",B2,"'",",")and then select that and drag it down, which creates =CONCATENATE("'",B3,"'",","), =CONCATENATE("'",B4,"'",","), etc. then highlight that whole column, copy paste to a plain text editor and paste back if needed, thus stripping the row separation. It works, but again, just as a one time deal, this is not a good solution for someone who needs this all the time.

我总是只输入=CONCATENATE("'",B2,"'",",")然后选择它并将其向下拖动，这会创建=CONCATENATE("'",B3,"'",","),=CONCATENATE("'",B4,"'",",")等，然后突出显示整个列，将粘贴复制到纯文本编辑器并在需要时粘贴回来，从而剥离行分隔。它有效，但同样，就像一次性交易一样，对于一直需要它的人来说，这不是一个好的解决方案。

回答by Nick Allen

I know this is really a really old question, but I was trying to do the same thing and I stumbled upon a new formula in excel called "TEXTJOIN".

我知道这真的是一个非常古老的问题，但我试图做同样的事情，我偶然发现了 Excel 中一个名为“TEXTJOIN”的新公式。

For the question, the following formula solves the problem

对于问题，下面的公式解决了问题

=TEXTJOIN("",TRUE,(a1:a4))

The signature of "TEXTJOIN" is explained as TEXTJOIN(delimiter,ignore_empty,text1,[text2],[text3],...)

“TEXTJOIN”的签名解释为 TEXTJOIN(delimiter,ignore_empty,text1,[text2],[text3],...)