如何将一列中的行合并到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 函数(感谢 Jean 的命名建议！).它将采用一系列单元格(任何维度、任何方向等)并将它们合并为一个字符串.作为可选的第三个参数，您可以添加一个分隔符(如空格或带锯齿的逗号).

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

在这种情况下，你会写这个来使用它:

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

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