Concat Excel列单元格每行带逗号 [英] Concat Excel Column Cell Strings per row with comma

问题描述

我有一个excel文件与一些列。我一直在尝试合并列，并用逗号分隔单元格字符串。

所以，我想要一个新的表，每行的连接结果：

  = CONCATENATE（A2，，，B2，，C2 ，D2）
 = CONCATENATE（A3，，，B3，，，C3，，，D3）
 = CONCATENATE（A4，，，B4，， ，，，D4）
  

我试图使用VBA，但没有成功：

  Sub sisk（）
 Dim sisk As String 
 
对于i = 2至4 
 sisk = CONCATENATE（Range（Cells（1，i）.Value），Cells（1,4）.Value）
 Next i 
 Worksheets（Sheet1）。Cells（1,6）= sisk 
 End Sub 
  

解决方案

大多数工作表函数可以在VBA中使用：

  Application.WorksheetFunction.Sum（1，2）
  

但是，一些工作表函数不能在VBA中使用， d不幸的是， CONCATENATE 是其中之一。

& 是VBA中用于连接字符串的操作符。

---

以下代码适用于您：

  Sub sisk（）
 Dim sisk As String 
 Dim row As Long 
 Dim col As Long 
 
对于行= 2到4 
 sisk = vbNullString 
对于col = 1到4 
如果VBA.Len（sisk）Then sisk = sisk& ，
 sisk = sisk&单元格（行，列）
下一列
 
工作表（Sheet1）。单元格（行，6）= sisk 
 
下一行
 
 
 End Sub 
  

I have an excel file with some columns. I have been trying to merge the columns and keep the cell strings separated with a comma.

So, I want a new table with the concat results per row :

=CONCATENATE(A2,",",B2,",",C2,",",D2)
=CONCATENATE(A3,",",B3,",",C3,",",D3)
=CONCATENATE(A4,",",B4,",",C4,",",D4)

I tried to use VBA but with no success :

Sub sisk()
Dim sisk As String

For i = 2 To 4
    sisk = CONCATENATE(Range(Cells(1, i).Value), Cells(1, 4).Value)
Next i
Worksheets("Sheet1").Cells(1, 6) = sisk
End Sub

解决方案

Most of the worksheet functions can be used in VBA like that:

Application.WorksheetFunction.Sum(1, 2)

However, some worksheet functions cannot be used in VBA, and unfortunately CONCATENATE is one of them.

& is operator used in VBA to concatenate strings.

---

The code below should work for you:

Sub sisk()
    Dim sisk As String
    Dim row As Long
    Dim col As Long

    For row = 2 To 4
        sisk = vbNullString
        For col = 1 To 4
            If VBA.Len(sisk) Then sisk = sisk & ","
            sisk = sisk & Cells(row, col)
        Next col

        Worksheets("Sheet1").Cells(row, 6) = sisk

    Next row


End Sub

这篇关于Concat Excel列单元格每行带逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！