在Excel中添加一个单元格范围的文本

I want to concatenate a range of cells into one cell, without macros/vbscript.

我想将一系列单元格连接到一个单元格中，不使用宏/vbscript。

The formula CONCATENATE() gets individual cells.

CONCATENATE()公式获取单个单元格。

2 个解决方案

#1

Its not that easy, but I end up with a solution that works wonders!

这并不容易，但我最终找到了一个神奇的解决方案!

A1: the text to search

A1:搜索文本。

B1:BN: The range within the results would go

B1:BN:在结果范围内

B5: The delimiter text

B5:分隔符的文本

=MID($A$1,LEN(CONCAT($B$1:B1))+COUNTA($B$1:B1)*LEN($B$5)+1,
    SEARCH(
        $B$5,
        $A$1,
        LEN(
           CONCAT($B$1:B1)) + COUNTA($B$1:B1)*LEN($B$5)+1)
           -(LEN(CONCAT($B$1:B1))+COUNTA($B$1:B1)*LEN($B$5)+1))

As for now it works perfect. Note that you can use whatever text as delimiter. In my case it was "comma + space".

就目前而言，它是完美的。注意，您可以使用任何文本作为分隔符。在我的例子中是“逗号+空格”。

Where Concat is a simple function that concatenates a range of cells:

其中Concat是连接一系列单元格的简单函数:

Function Concat(myRange As Range) As String
  Dim r As Range
  Application.Volatile
  For Each r In myRange
    If Len(r.Text) Then
      Concat = Concat & IIf(Concat <> "", "", "") & r.Text
    End If
  Next
End Function

#2

You can either use CONCATENATE() or & to join cells. There is no range you can grab all at once unless you use a UDF such as MCONCAT.

您可以使用CONCATENATE()或&连接单元。除非你使用像MCONCAT这样的UDF，否则你不可能同时获取所有的范围。

#1