vba - 使用 VBA 过滤列内容

Question

我有一列将项目尺寸与其产品名称混合在一起，类似于:

               A
47"H X 18"W Six-Light ChandW Three-Light Pendant
29"H X 38"W X 33"D Eight-Lt Chandelier
40"H X 32"W Four-Light Chand
43"H X 36"W X 29"D Three-Light Sconce
60"H X 50"W Eighteen-Light Chand

... // another 600 rows

我正在尝试使用 Magmi 将其导入我们的商店.我想将产品名称与其尺寸分开。

问题是有些项目只有宽度/高度而没有深度，而其他项目(大多数)都有这三个。对列使用文本似乎不足以过滤这些数据，所以我假设我需要使用 VBA。

作为 VBA 的新手，我不确定从哪里开始。有没有人有想法/指针？

Answer 1

The problem is that some items only have a width/height and no depth while others (most of them) have all three.

逻辑:

查找 "的号码它的位置。如果没有第三个 "然后寻找 " "第二次后 "如果有第三个 " ，然后在此之后寻找一个空格。

假设您的数据看起来像这样



代码:

这是你正在尝试的吗？

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim Delim As String
    Dim Pos1 As Long, Pos2 As Long, Pos3 As Long
    Dim i As Long, lRow As Long

    '~~> This is "
    Delim = Chr(34)

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Last Row of Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            '~~> Get 1st position of "
            Pos1 = InStr(1, .Range("A" & i).Value, Delim)
            '~~> Get 2nd position of "
            Pos2 = InStr(Pos1 + 1, .Range("A" & i).Value, Delim)
            '~~> Get 3rd position of "
            Pos3 = InStr(Pos2 + 1, .Range("A" & i).Value, Delim)

            '~~> Check if 3rd " is present
            If Pos3 = 0 Then
                Pos1 = InStr(Pos2 + 1, .Range("A" & i).Value, " ")
            Else
                Pos1 = InStr(Pos3 + 1, .Range("A" & i).Value, " ")
            End If

            '~~> Extract relevant text into Col B
            .Range("B" & i).Value = Mid(.Range("A" & i).Value, Pos1 + 1)
        Next i
    End With
End Sub

输出: