☰

将两个单元格的对应值连接起来，并用逗号分隔多个值

tag-icon • tag-icon concatenation
将两个单元格的对应值连接起来，并用逗号分隔多个值

在 Excel 表中，我有多个以逗号分隔的单元格。我想连接两个单元格的相应值。示例：

Cell_A1 (A,B,C,D) and Cell_B1 (E,F,G,H)  
Then in Cell_C1 it should be (AE,BF,CG,DH).

答案1

这样做

Option Explicit

Sub DoTheThing()

Dim row As Integer
row = 1 ' WHAT IS THE STARTING ROW

'Let's clear the answers

Range("D:F").Cells.Clear

Do While (Range("A" & row).Value <> "")

    Dim lookUpValue As String
    lookUpValue = Range("A" & row).Value

    Dim vals() As String
    vals = Split(Range("B" & row).Value, ",")

    Dim result As String
    result = ""

    Dim i As Integer

    For i = 0 To UBound(vals)

        If CSng(lookUpValue) >= CSng(vals(i)) Then
            result = result & "Yes, "
        Else
            result = result & "No, "
        End If

    Next i

    result = Trim(result)
    result = Left(result, Len(result) - 1)

    Range("D" & row).Value = result

    result = "" ' reset it
     Dim valD() As String
    valD = Split(Range("C" & row).Value, ",")

     For i = 0 To UBound(valD)

        If CSng(lookUpValue) <= CSng(valD(i)) Then
            result = result & "Yes, "
        Else
            result = result & "No, "
        End If

    Next i

    result = Trim(result)
    result = Left(result, Len(result) - 1)

    Range("E" & row).Value = result


'finally

    Dim splitD() As String
    splitD = Split(Range("D" & row).Value, ",")


    Dim splitE() As String
    splitE = Split(Range("E" & row).Value, ",")

    result = ""

    Dim length As Integer
    length = UBound(splitD)
    For i = 0 To length 'both should have the same values according to @Raw
        If (i = length) Then
            Range("F" & row).Value = Range("F" & row).Value & splitD(i) & splitE(i)
        Else
            Range("F" & row).Value = Range("F" & row).Value & splitD(i) & splitE(i) & ","
        End If
    Next i

    row = row + 1

Loop

End Sub

前

在此处输入图片描述

后

在此处输入图片描述

答案2

好问题。请在下图中查找答案。
注意 1：在要拆分的单元格中添加逗号作为最后一个字符 - 否则#value最后一行会出现错误。
拆分单元格的内容

现在您必须对另一个单元格执行相同的操作并获得类似的结果。

最后，使用concatenate函数连接两列中的对并获取最终结果。
连接看起来会像这样（假设单元格 A2 包含逗号字符）
=concatenate(C3,$A$2,E3)还假设 E3 包含必须与 C3 连接的结果。
如果您有任何问题，请随时发表评论。

相关内容