Using VBA to extract data between mismatched delimiters

[NoleJenny]

I'm relatively inexperienced with VBA. I'm trying to use it to extract size and quantity data from a string. The size is found after the second ":" and before the first ")". Here's an example of one of the text strings, but I'll be doing this for an entire column of data.

First Unitarian Church of Dallas T-shirt (Amount: 10.00 USD, Quantity: 2, Size: Adult 2XL) Total: $20.00

Here's what I have so far, but I'm getting a "subscript out of range" error on the italicized row.

Sub ExtractSizesAndQuantities()

        Dim lastRow As Long
            lastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        Dim Size As String
        Dim i As Integer
        Dim Text As String

        For i = 2 To lastRow
            Text = Sheets("Orders").Range("D" & i)
            Size = Split(Text, ":")(2)
            Sheets("Orders").Range("F" & i) = Size
            Next i

End Sub

This is my first time posting, so let me know if I've left anything out. Thanks!

 

[Joe4]

Welcome to the Board!

Do you need VBA at all? Maybe you can use the new TEXTBEFORE, TEXTAFTER, and TEXTSPLIT functions in Excel, i.e.

Formula in cell B2:

=TEXTBEFORE(TEXTAFTER(A2,"Quantity: "),",")

Formula in cell C2:

=TEXTBEFORE(TEXTAFTER(A2,"Size: "),")")

 

[NoleJenny]

I'm relatively inexperienced with VBA. I'm trying to use it to extract size and quantity data from a string. The size is found after the second ":" and before the first ")". Here's an example of one of the text strings, but I'll be doing this for an entire column of data.

First Unitarian Church of Dallas T-shirt (Amount: 10.00 USD, Quantity: 2, Size: Adult 2XL) Total: $20.00

Here's what I have so far, but I'm getting a "subscript out of range" error on the italicized row.

Sub ExtractSizesAndQuantities()

Dim lastRow As Long
lastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Dim Size As String
Dim i As Integer
Dim Text As String

For i = 2 To lastRow
Text = Sheets("Orders").Range("D" & i)
Size = Split(Text, ":")(2)
Sheets("Orders").Range("F" & i) = Size
Next i

End Sub

This is my first time posting, so let me know if I've left anything out. Thanks!

Welcome to the Board!

Do you need VBA at all? Maybe you can use the new TEXTBEFORE, TEXTAFTER, and TEXTSPLIT functions in Excel, i.e.
View attachment 115056

Formula in cell B2:

=TEXTBEFORE(TEXTAFTER(A2,"Quantity: "),",")

Formula in cell C2:

=TEXTBEFORE(TEXTAFTER(A2,"Size: "),")")

That works! I guess I was overcomplicating it lol. Thank you!

 

[Joe4]

You are welcome!
Glad I was able to help!