SOS, hoping someone can provide assistance to this challenge I have failed to overcome.
I require to identify specific text nestled between the character 'OR' from a string
Example;
Cell A2 = OR Apprentice OR Assistant OR Professional OR Trainee OR
Answer;
Cell B2 = Apprentice
Cell C2 = Assistant
Cell D2 = Professional
Cell E2 = Trainee
I thought the below VBA code would work but alas the reoccuring 'OR' I suspect is causing the error message for the code below. Any help to fine tune the below code will be gratefully received.
PS> I do not have access to Power Query.
_ _ _
Sub Extract()
Application.ScreenUpdating = False
Dim AR() As Variant: AR = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim SP() As String
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim tmp As String
Dim r As Range
For i = LBound(AR) To UBound(AR)
SP = Split(AR(i, 1), "OR")
For j = 1 To UBound(SP)
tmp = tmp & Left(SP(j), InStr(SP(j), "OR") - 1) & "@"
Next j
AL.Add Left(tmp, Len(tmp) - 1)
tmp = vbNullString
Next i
Set r = Range("B2").Resize(AL.Count, 1)
With r
.Value = Application.Transpose(AL.ToArray)
.TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="@"
End With
Application.ScreenUpdating = True
End Sub
I require to identify specific text nestled between the character 'OR' from a string
Example;
Cell A2 = OR Apprentice OR Assistant OR Professional OR Trainee OR
Answer;
Cell B2 = Apprentice
Cell C2 = Assistant
Cell D2 = Professional
Cell E2 = Trainee
I thought the below VBA code would work but alas the reoccuring 'OR' I suspect is causing the error message for the code below. Any help to fine tune the below code will be gratefully received.
PS> I do not have access to Power Query.
_ _ _
Sub Extract()
Application.ScreenUpdating = False
Dim AR() As Variant: AR = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim SP() As String
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim tmp As String
Dim r As Range
For i = LBound(AR) To UBound(AR)
SP = Split(AR(i, 1), "OR")
For j = 1 To UBound(SP)
tmp = tmp & Left(SP(j), InStr(SP(j), "OR") - 1) & "@"
Next j
AL.Add Left(tmp, Len(tmp) - 1)
tmp = vbNullString
Next i
Set r = Range("B2").Resize(AL.Count, 1)
With r
.Value = Application.Transpose(AL.ToArray)
.TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="@"
End With
Application.ScreenUpdating = True
End Sub
Last edited: