将列中的文本拆分为两个单独的列

I am having trouble splitting the text on the Location into two separate columns based on the "-". The code needs to loop through each row and split the cell values.

我无法将位置上的文本拆分为基于“ - ”的两个单独的列。代码需要遍历每一行并拆分单元格值。

Code:

'Split Location into 2 Columns
    txt = Sheet4.Cells(i, 10).Value
    Location = Split(txt, "-")

    For i = 2 To LastRow2
        For j = 0 To UBound(Location)
            Cells(1, j + 1).Value = Location(j)
        Next j
    Next i

Sample Sheet: 将列中的文本拆分为两个单独的列

2 个解决方案

#1

The code below assumes the data you want to manipulate is on your active sheet and you want to split the data on your current sheet and place it into column K.

下面的代码假定您要操作的数据位于活动工作表上,并且您希望将当前工作表上的数据拆分并将其放入K列。

I would use TextToColumns instead of a loop

我会使用TextToColumns而不是循环

Range("J2:J" & LastRow2).TextToColumns Destination:=Range("J2"), _
                                       OtherChar:="-", _
                                       FieldInfo:=Array(Array(1, 1), Array(2, 1))

If you want to use a loop you can include the txt / location variables inside the loop and use i / j as offsets for data placement

如果要使用循环,可以在循环中包含txt /位置变量,并使用i / j作为数据放置的偏移量

For i = 2 To LastRow2
    'Split Location into 2 Columns
    txt = Cells(i, 10).Value
    Location = Split(txt, "-")

    For j = 0 To UBound(Location)
        Cells(i, 10 + j).Value = Location(j)
    Next j
Next i

#2

I think the problem is just the Sheet4.Cells(i, 10).Value that you put outside the loop. Try something like this:

我认为问题只是Sheet4.Cells(i,10).Value你放在循环之外。尝试这样的事情:

For i = 2 To LastRow2
    'Split Location into 2 Columns
    txt = Sheet4.Cells(i, 10).Value
    Location = Split(txt, "-")

    For j = 0 To UBound(Location)
        Cells(1, j + 1).Value = Location(j)
    Next j
Next i

#1