如何将“A,B,C”和“X,Y,Z”等列拆分并分配为“A,X,B,Y,C,Z”？

Question 1

如果您愿意使用 VBA，这将是一个相对简单的解决方案。是的，它使用 VBA，但我认为它比使用命名范围的解决方法更容易遵循。代码如下。您可以在工作表中使用类似的函数调用此函数=BlendCells(", ",A2,B2)。第一个参数是，strDelimiter它允许您输入文本的分隔方式。在您的示例中，这将是一个逗号和一个空格。接下来的两个参数是您想要混合的两个单元格。

Option Explicit

Public Function BlendCells(strDelimiter As String, Range1 As Range, Range2 As Range) As String

    'Declarations
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long

    'Setup each array
    arr1 = Split(Range1.Value, strDelimiter)
    arr2 = Split(Range2.Value, strDelimiter)

    'Blend the text
    For i = Application.Min(LBound(arr1), LBound(arr2)) To Application.Max(UBound(arr1), UBound(arr2))
        If i <= UBound(arr1) Then BlendCells = BlendCells & arr1(i) & strDelimiter
        If i <= UBound(arr2) Then BlendCells = BlendCells & arr2(i) & strDelimiter
    Next

    'Trim the results
    BlendCells = Left(BlendCells, Len(BlendCells) - Len(strDelimiter))

End Function

Answer

如果您愿意使用 VBA，这将是一个相对简单的解决方案。是的，它使用 VBA，但我认为它比使用命名范围的解决方法更容易遵循。代码如下。您可以在工作表中使用类似的函数调用此函数=BlendCells(", ",A2,B2)。第一个参数是，strDelimiter它允许您输入文本的分隔方式。在您的示例中，这将是一个逗号和一个空格。接下来的两个参数是您想要混合的两个单元格。

Option Explicit

Public Function BlendCells(strDelimiter As String, Range1 As Range, Range2 As Range) As String

    'Declarations
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long

    'Setup each array
    arr1 = Split(Range1.Value, strDelimiter)
    arr2 = Split(Range2.Value, strDelimiter)

    'Blend the text
    For i = Application.Min(LBound(arr1), LBound(arr2)) To Application.Max(UBound(arr1), UBound(arr2))
        If i <= UBound(arr1) Then BlendCells = BlendCells & arr1(i) & strDelimiter
        If i <= UBound(arr2) Then BlendCells = BlendCells & arr2(i) & strDelimiter
    Next

    'Trim the results
    BlendCells = Left(BlendCells, Len(BlendCells) - Len(strDelimiter))

End Function

Question 2

要将子字符串放入各个列中，您可以使用：

C1:  =INDEX(TRIM(MID(SUBSTITUTE(OFFSET($A$1,0,MOD(COLUMNS($A:A)+1,2)),",",REPT(" ",99)),{1,99,198},99)),MOD(COLUMNS($A:A)-1,3)+1)

并填充至 I1 左侧。

或者，您也可以使用数据/文本到列工具，以逗号作为分隔符。

为了获得最终结果，您可以连接各个列：

=B1 & "," & C1 & "," & D1 & "," & E1 & "," & F1 & "," & G1 & "," & H1 & "," & I1

或者，如果您确实不需要单独的列，则可以使用单个公式：

J1: =CONCATENATE(
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),1),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),1),",",
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),2),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),2),",",
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),3),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),3))

如果段数超过三，只需扩展数组常量和连接公式即可。如果段数可变，VBA 解决方案会更简单，具体取决于您想要什么；数据的性质、输出的位置等。

Answer