Excel VBA Code to strip IdNumbers and Names from a long string imported from another system

Option Explicit
Option Base 0

Public Sub test()
    Dim rawData As String
    Dim row(0 To 1) As String
    Dim data As Collection
    Dim rx As Object
    Dim matchs As Object
    Dim match As Object
    
    rawData = ThisWorkbook.Worksheets(1).Range("C2").Value
    Set data = New Collection
    Set rx = CreateObject("VBScript.Regexp")
    rx.MultiLine = True
    rx.Global = True
    rx.Pattern = "(?:(\d{5}) )?([^\r\n""]+)$"
    Set matchs = rx.Execute(rawData)
    For Each match In matchs
        row(0) = match.SubMatches(0)    '// employeeID if it exist
        row(1) = match.SubMatches(1)    '// employee name
        data.Add row
    Next
    Set matchs = Nothing
    Set rx = Nothing
    
    '// do whatever you want with the data collection
    
        '// cleanup
    Set data = Nothing
End Sub

Select allOpen in new window

Option Explicit

Sub GetDataFromString()
    Dim ws As Worksheet
    Dim arStr() As String
    Dim rw As Integer
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    arStr = Split(ws.Range("C2").Value, Chr(10))
    For rw = LBound(arStr, 1) To UBound(arStr, 1)
        If Len(arStr(rw)) > 0 Then
            If Asc(Left(arStr(rw), 1)) >= 48 And Asc(Left(arStr(rw), 1)) <= 57 Then
                ws.Range("B" & rw + 6) = Left(arStr(rw), InStr(1, arStr(rw), " ") - 1)
                ws.Range("C" & rw + 6) = Mid(arStr(rw), InStr(1, arStr(rw), " ") + 1, 100)
            Else
                ws.Range("C" & rw + 6) = arStr(rw)
            End If
        End If
    Next rw
End Sub

Select allOpen in new window