VBA Code to Split Text

Question

Hello,&nbsp;&nbsp;My place of employee switched programs and there is currently not a way to extract data. I'm looking to insert a VBA code that splits the Job Title and Job Req # into seperate cells.&nbsp;&nbsp;All job reqs start with a P. Here is a sample position/req.Material HandlerP25-116021-2&nbsp;Thank you!

hansvogelaar · Accepted Answer

zackwilson11&nbsp;
Select the range before running the following macro:
Sub SplitText()
    Dim r As Range
    Dim s As String
    Dim re As Object
    Dim m As Object
    Dim p As Long
    Application.ScreenUpdating = False
    ' Optional - insert column to the right
    Selection.Offset(0, 1).EntireColumn.Insert
    Set re = CreateObject(Class:="VBScript.RegExp")
    re.Pattern = "P\d"
    For Each r In Selection
        s = r.Value
        Set m = re.Execute(s)
        If m.Count &gt; 0 Then
            p = m(0).FirstIndex
            r.Value = Left(s, p)
            r.Offset(0, 1).Value = Mid(s, p + 1)
        End If
    Next r
    Set m = Nothing
    Set re = Nothing
    Application.ScreenUpdating = True
End Sub

Forum Discussion

VBA Code to Split Text

Resources