VBA Code to Split Text

[zackwilson11]

Hello, 

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. 

All job reqs start with a P. Here is a sample position/req.

Material HandlerP25-116021-2

Thank you!

[#2 · Accepted solution]

@zackwilson11 

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 > 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

[#3]

Thank you!!