macro to pull some text from cells in a column

[tonywatsonhelp]

Hi Everyone, have a problem with this hope you can help,

I'm looking for a macro that can Look down range C3:C and last row and if the cell contains the words "Add Ons:" take all the text after it and paste it in column L but if the extracted text has ";" in it only extract up to there (not including)

so if the words are:
Add Ons: Fuji Apple Salad
i want Fuji Apple Salad
but if its
"Add Ons: Coconut Street Corn;
Choose Sauce: Sweet Chili"
i just want Coconut Street Corn

please help if you can
Thanks
Tony

Add Ons: Coconut Street Corn; Choose Sauce: Sweet Chili

Add Ons: Fuji Apple Salad

 

[Georgiboy]

One option as a formula and not VBA:

Book1
	A	B
2	Add Ons: Coconut Street Corn; Choose Sauce: Sweet Chili	Coconut Street Corn
3	Add Ons: Fuji Apple Salad	Fuji Apple Salad
4	Fuji Apple Salad
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=BYROW(A2:A4,LAMBDA(x,LET( ts,TEXTSPLIT(CLEAN(x),{":",";"}), IF(INDEX(ts,,1)="Add Ons",TRIM(INDEX(ts,,2)),""))))
Dynamic array formulas.

 

[mumps]

Try:

Sub ExtractSuString()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("C3", Range("C" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        If InStr(v(i, 1), "Add Ons:") = 1 And InStr(v(i, 1), ";") = 0 Then
            Range("L" & i + 2) = Trim(Split(v(i, 1), ":")(1))
        ElseIf InStr(v(i, 1), "Add Ons:") = 1 And InStr(v(i, 1), ";") = 1 Then
            Range("L" & i + 2) = Trim(Split(Split(v(i, 1), ":")(1), ";")(0))
        End If
    Next i
    Application.ScreenUpdating = False
End Sub