vba - 获取单元格中文本的子部分

Question

我在 Excel 中有一个电子表格，每行一个产品，我想根据列中的文本获取每个项目的高度和宽度。我在 A 列中有尺寸文本:

8" H x 8" W 
2.5" H x 3.5" W 
per side 8" H x 8" W 
1 color left side 1" H x 3" W 

并想将高度放在列 (B) 中，将宽度放在列 (C) 中。像这样:

              (A)                   (B)                (C)
8" H x 8" W                         8                  8
2.5" H x 3.5" W                     2.5                3.5
per side 8" H x 8" W                8                  8
1 color left side 1" H x 3" W       1                  3

我从这个片段开始，但我不知道如何获得所需的部分:

Sub numberExtractor()
Dim Cell As Integer
For Cell = 2 To 449

    If InStr(Cells(Cell, 17), """ H") > 0 Then
        Cells(Cell, 18).Value = Left(Cells(Cell, 17), InStr(Cells(Cell, 17), " ") - 1)
    End If
    If InStr(Cells(Cell, 17), """ W") > 0 Then
        Cells(Cell, 19).Value = Right(Cells(Cell, 17), InStr(Cells(Cell, 17), " ") + 2)
    End If

Next Cell
End Sub

Answer 1

逻辑:

使用唯一的分隔符。我正在使用 SIDZ .

替换 " W通过 ""

替换 " H x通过 SIDZ

第一次拆分于 SPACE然后是唯一的分隔符

尝试这个。

Option Explicit

Sub Sample()
    Dim rng As Range, aCell As Range
    Dim Ar
    Dim Ht As Double, Wt As Double
    Dim sTemp As String, sDelim As String
    Dim i As Long

    sDelim = "SIDZ"

    Set rng = Range("A1:A4")

    For Each aCell In rng
        sTemp = aCell.Value
        sTemp = Replace(Replace(sTemp, """ W ", ""), """ H x ", sDelim)

        Ar = Split(sTemp)

        For i = LBound(Ar) To UBound(Ar)
            If Ar(i) Like "*" & sDelim & "*" Then
                aCell.Offset(, 1).Value = Split(Ar(i), sDelim)(0)
                aCell.Offset(, 2).Value = Split(Ar(i), sDelim)(1)
            End If
        Next i
    Next aCell
End Sub