从一系列单元格中提取第一个单词或第一个和第二个单词

Question

我有一个用空格分隔文字(单词和数字)的专栏。有两种情况：

案例1(3个单词由2个空格分隔)：BALDOR 3 hp-4

案例2(4个字由3个空格隔开)：US ELECTRICAL 75 hp-232

我需要提取粗体单词(它们在我所拥有的数据中没有粗体，只是为了说明)，所以我想我会颠倒单词的顺序，然后去掉前两个单词(3 hp4和75 hp232)，它总是输出粗体单词。

我可能用错误的方法来颠倒单词的顺序，所以如果你有另一种方法，你认为最好告诉你。

到目前为止，这就是我所拥有的：

    Sub ExtractMissingInfo2()

    Dim TypeCell As Variant
    Dim Manufacturer As String
    Dim MFG As Variant
    Dim MFGrev As Variant
    Dim MFGout As Variant
    Dim RowCount As Variant
    Dim Rng As Range
    Dim a As Variant
    Dim I As Variant
    Dim wbdata As Workbook
    Dim wsData As Worksheet

    Set wbdata = Workbooks("trial1")
    Set wsData = wbdata.Worksheets("Final Data")

wsData.Activate

'Counts how many cells in the chosen column
RowCount = Cells(Rows.Count, 4).End(xlUp).Row

For a = 2 To RowCount 
    If Not IsEmpty(Cells(a, 4)) Then
        TypeCell = wsData.Cells(a, 4).Text     'cells with information
        MFG = Split(TypeCell, " ")             'separate them 

    'Reverse the order of the words    
    For I = UBound(MFG) To 0 Step -1
            MFGrev = MFGrev + "" + MFG(I) + " "

           'Use the last iteration which will include all the words in reverse order
                If I = 0 Then
                MFGout = MFGrev
            End If
        Next

    'This part I am not sure about
    Manufacturer = Split(MFGout.Text, " ")(0)

    'Insert extracted words into new column
    Cells(a, 16) = WorksheetFunction.Transpose(Manufacturer)

    Else
    MsgBox ("Is empty... row " & a)
    End If
Next

End Sub

所以我的第一个问题是，当循环时，它会不断地将每个单元格的每一个字符串添加到下一个单元格中，而不是一个一个地遍历每个单元格，然后以相反的顺序输出单词。

我的第二个问题是，我不知道如何在撤销命令后删除前两个字。

这是我的第一个问题，所以如果我在格式上出错，请告诉我。

提前感谢您的帮助！

编辑:我要做的是提取制造商的名字作为设备清单。名字中可以有一两个单词，所以这就是我需要提取的。然后我把这些贴在另一个专栏里。

我给出的案例中仅举了几个例子，说明了清单中出现的两个案例，并询问了如何处理它们。

Answer 1

我想你是在追求这段代码(注释中的解释)

Option Explicit

Sub ExtractMissingInfo2()
    Dim MFG As Variant
    Dim cell As Range

    With Workbooks("trial1").Worksheets("Final Data") 'reference your wanted workbook and worksheet
        For Each cell In .Range("D2", .Cells(Rows.Count, 4).End(xlUp)).SpecialCells(xlCellTypeConstants) 'loop thorugh referenced sheet column D not empty cells from row 2 down to last not empty row

            MFG = Split(cell.Text, " ") ' separate each word

            If UBound(MFG) > 1 Then ReDim Preserve MFG(0 To UBound(MFG) - 2) ' if there were more than two words, keep all but the last two ones

            cell.Offset(, 12).Value = Join(MFG, " ") ' write remaining words into column P same row of current cell
        Next
    End With
End Sub

Answer 2

尝试这段代码，这两种情况都有效：

Sub test()

Dim myarray As Variant
myarray = Array("US ELECTRICAL 3 hp-2", "BALDOR 3 hp-4")


For j = 0 To UBound(myarray)
    x = ""
    t = Split(myarray(j))

    For i = 0 To UBound(t) - 2
        x = x & " " & t(i)
    Next i

    MsgBox myarray(j) & " ---- " & x
Next j

End Sub

Answer 3

假设您想要删除A1中字符串的前3个字母，请给它一个加号：

Dim n As Integer
n = 3
Cells(2, 2).Value = Right(Range("A1"), Len(Range("A1")) - n)

这假设字符串的第一部分的长度是常量，这似乎是您的示例中的长度，尽管您可能希望澄清这一点。你在找什么还不太清楚。