我在Excel中有几百个单元格,想要将它们连接成一个字符串。除了手动一个一个地输入它们并键入CONCATENATE(A1, A2, ....)
之外,是否有更简单的方法?
CONCATENATE(A1:A255)
不起作用。
我在Excel中有几百个单元格,想要将它们连接成一个字符串。除了手动一个一个地输入它们并键入CONCATENATE(A1, A2, ....)
之外,是否有更简单的方法?
CONCATENATE(A1:A255)
不起作用。
*在新标签页中,在A1单元格中键入A1
*在A2单元格中输入A2
*使用填充系列来完成列A中的值
*在B1单元格中键入A1
在B2单元格中使用此公式
= B1 & "," & A2
将公式复制到下面的单元格中
复制并粘贴值以获取您创建的值字符串。
A1 A1
A2 A1,A2
A3 A1,A2,A3
A4 A1,A2,A3,A4
A5 A1,A2,A3,A4,A5
A6 A1,A2,A3,A4,A5,A6
A7 A1,A2,A3,A4,A5,A6,A7
A8 A1,A2,A3,A4,A5,A6,A7,A8
A9 A1,A2,A3,A4,A5,A6,A7,A8,A9
A10 A1,A2,A3,A4,A5,A6,A7,A8,A9,A10
=B1 & ", "","" ," & A2
- cobberboy按下 Alt-F11
,插入新模块,粘贴下面的代码。
Public Function concatRange(data As Range, Optional sep As String = "") As String
Dim ret As String
Dim sep2 As String
ret = ""
sep2 = ""
For Each cell In data
ret = ret & sep2 & cell.Value
sep2 = sep
Next cell
concatRange = ret
End Function
使用方法:
=concatRange(A8:D11;", ") 'OS with ; list separator
=concatRange(A8:D11,", ") 'OS with , list separator or in a macro code
或者=concatRange(A8:D11)
=Range2Csv(A1:A255,"|")
使用 Alt+F11
打开 VBA 编辑器,将代码放入模块中。
代码片段如下:
Option Explicit
'**********************************************
'* PURPOSE: Concatenates range contents into a
'* delimited text string
'*
'* FUNCTION SIGNATURE: Range2Csv(Range, String)
'*
'* PARAMETERS:
'* Range - the range of cells whose contents
'* will be included in the CSV result
'* String - delimiter used to separate values
'* (Optional, defaults to a comma)
'*
'* AUTHOR: www.dullsharpness.com
'*
'* NOTES: [add'l notes removed for brevity]
'*
'**********************************************
Public Function Range2Csv(inputRange As Range, Optional delimiter As String)
Dim concattedList As String 'holder for the concatted CSVs
Dim rangeCell As Range 'holder cell used in For-Each loop
Dim rangeText As String 'holder for rangeCell's text
'default to a comma delimiter if none is provided
If delimiter = "" Then delimiter = ","
concattedList = "" 'start with an empty string
'Loop through each cell in the range to append valid contents
For Each rangeCell In inputRange.Cells
rangeText = rangeCell.Value 'capture the working value
'Only operate on non-blank cells (i.e. Length > 0)
If Len(rangeText) > 0 Then
'Strip any delimiters contained w/in the value itself
rangeText = WorksheetFunction.Substitute(rangeText, delimiter, "")
If (Len(concattedList) > 0) Then
'prepend a delimiter to the new value if we
'already have some list items
concattedList = concattedList + delimiter + rangeText
Else
'else if the list is blank so far,
'just set the first value
concattedList = rangeText
End If
End If
Next rangeCell
'Set the return value
Range2Csv = concattedList
End Function
concatenate(a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19, a20, a21, a22, a23, a24, a25, a26, a27, a28, a29, a30, a31, a32, a33, a34, a35, a36, a37, a38, a39, a40, a41, a42, a43, a44, a45, a46, a47, a48, a49, a50, a51, a52, a53, a54, a55, a56, a57, a58, a59, a60, a61, a62, a63, a64, a65, a66, a67, a68, a69, a70, a71, a72, a73, a74, a75, a76, a77, a78, a79, a80, a81, a82, a83, a84, a85, a86, a87, a88, a89, a90, a91, a92, a93, a94, a95, a96, a97, a98, a99, a100, a101, a102, a103, a104, a105, a106, a107, a108, a109, a110, a111, a112, a113, a114, a115, a116, a117, a118, a119, a120, a121, a122, a123, a124, a125, a126, a127, a128, a129, a130, a131, a132, a133, a134, a135, a136, a137, a138, a139, a140, a141, a142, a143, a144, a145, a146, a147, a148, a149, a150, a151, a152, a153, a154, a155, a156, a157, a158, a159, a160, a161, a162, a163, a164, a165, a166, a167, a168, a169, a170, a171, a172, a173, a174, a175, a176, a177, a178, a179, a180, a181, a182, a183, a184, a185, a186, a187, a188, a189, a190, a191, a192, a193, a194, a195, a196, a197, a198, a199, a200, a201, a202, a203, a204, a205, a206, a207, a208, a209, a210, a211, a212, a213, a214, a215, a216, a217, a218, a219, a220, a221, a222, a223, a224, a225, a226, a227, a228, a229, a230, a231, a232, a233, a234, a235, a236, a237, a238, a239, a240, a241, a242, a243, a244, a245, a246, a247, a248, a249, a250, a251, a252, a253, a254, a255)
连接(a1, a2, a3, ..., a255)。
来点PowerShell!
"concatenate(a$((1..255) -join ', a'))" | clip
打开文本文件并复制粘贴
这个 VBA 函数可以将单元格的内容连接起来,如果需要,可以使用可选的分隔符。将其复制到标准模块中:
Option Explicit
Function Concat(CellRange As Range, Optional Delimiter As String) As String
' this function will concatenate a range of cells and return the result as a single string
' useful when you have a large range of cells that you need to concatenate
' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Dim retVal As String, dlm As String, cell As Range
retVal = ""
If Delimiter = Null Then
dlm = ""
Else
dlm = Delimiter
End If
For Each cell In CellRange
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
Concat = retVal
End Function
如果您想要使用纯Excel方法(即不使用VBA),那么James Jenkins提出的方法是最好的选择。 如果您愿意使用VBA,则打开VBA编辑器,添加一个新模块,并添加以下代码:
Option Explicit
Public Function JoinText(cells As Variant,Optional delim_str As String) As String
If cells.Columns.count < cells.Rows.count Then
JoinText = Join(WorksheetFunction.Transpose(cells), delim_str)
Else
JoinText = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(cells)), delim_str)
End If
End Function
要轻松打开VBA编辑器,请按Alt-F11键。 要插入模块,您需要右键单击“项目”窗口中列出的工作簿。
从Excel中调用该函数的方法如下:
=JoinText(A1:C1)
如果你想添加一个分隔符(例如逗号):
=JoinText(A1:C1,",")
这不是纯粹的Excel,但是有一种使用Word轻松完成此操作的方法。
CONCAT()
函数接受一个数组输入但却返回一个字符串值,因此您只需要按Enter键即可。 - Greedo如果您想要连接的值从工作表的第2行第3列开始
Sub GOWN()
roww = 2
Do While cells(roww, 2) <> ""
aa = cells(roww, 3)
dd = dd & aa & ","
roww = roww + 1
Loop
cells(roww + 1, 3) = dd
End Sub