如何将列中的行列入Excel中的一个单元格？ [英] How to merge rows in a column into one cell in excel?

问题描述

例如

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

我想将它们全部合并到一个单元格Iamaboy

此示例显示4个单元格合并到1个单元格，但是我有很多单元格（超过100个），我不能使用 A1& A2& A3& A4 我该怎么办？

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 & A4 what can I do?

推荐答案

我向你呈现我的ConcatenateRange VBA功能命名建议！）。它将需要一系列单元格（任何维度，任何方向等），并将它们合并为单个字符串。作为可选的第三个参数，您可以添加一个分隔符（如空格或逗号分隔）。

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).

在这种情况下，您可以写这个来使用它： / p>

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

=ConcatenateRange(A1:A4)

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

Dim cell As range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

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

ConcatenateRange = newString

End Function

这篇关于如何将列中的行列入Excel中的一个单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！