Excel 合并逗号分隔的单元格

Question

我有两个逗号分隔的单元格。它们都包含相同数量的信息。我需要按照以下格式合并它们：

单元格 1：A、B、C、D、E

单元格 2：1,2,3,4,5

结果：A,1,B,2,C,3,D,4,E,5

如何实现这一目标？

Answer 1

一步，一个公式：

=TEXTJOIN(",",TRUE,FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b")&","&FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","//b"))

FILTERXML 创建单元格文本数组，然后 TEXTJOIN 将它们放在一起。

Answer 2

您的问题非常模糊，所以我的回答反映了这一点。

一个简单的方法是使用文本到列来分隔值，然后使用简单的公式将它们加在一起：

=A1&","&A2&","&B1&","&B2&","&C1&","&C2&","&D1&","&D2&","&E1&","&E2

Answer 3

如果我理解正确的话，你的源数据是这种格式：

据我所知，用公式合并这两个单元格无法得到你想要的结果。一般只能按照这种格式合并：

=CONCATENATE(A1,",",A2)

根据我的研究，除非使用 VBA，否则无法直接获得结果。

我建议你先把细胞分开。

“日期”-“文本到列”

然后使用以下公式：

=CONCATENATE(A8,",",A9,",",B8,",",B9,",",C8,",",C9,",",D8,",",D9,",",E8,",",E9)

Answer 4

考虑：

Public Function Knit(s1 As String, s2 As String) As String
    Dim s As String, L As Long, U As Long, i As Long

    arr1 = Split(s1, ",")
    arr2 = Split(s2, ",")
    L = LBound(arr1)
    U = UBound(arr1)

    For i = L To U
        s = s & IIf(s = "", "", ",") & arr1(i) & "," & arr2(i)
    Next i
    Knit = s
End Function