Separating text from alphanumeric

Question

I want to separate text (names) from numbers (IDs), but there are some exceptions.

Code separates text from numbers but some of the IDs have a letter at the beginning.

How do I obtain full ID with a letter, if applicable?

Option Explicit

Sub NamesandID()

Dim RowNum As Long
Dim eChar As Integer

RowNum = 2
Do Until Cells(RowNum, 1).Value = ""

    For eChar = 1 To Len(Cells(RowNum, 1))
        If IsNumeric(Mid(Cells(RowNum, 1), eChar, 1)) = True Then
            Cells(RowNum, 3).Value = Cells(RowNum, 3).Value _
              & Mid(Cells(RowNum, 1), eChar, 1)
        Else
            Cells(RowNum, 2).Value = Cells(RowNum, 2).Value _
              & Mid(Cells(RowNum, 1), eChar, 1)
        End If
    Next

    RowNum = RowNum + 1
Loop

End Sub

Answer 1

My two cents.

---

1): Through formulae:

Formula in B2:

=LET(X,TEXTAFTER(TEXTBEFORE(A2:A5,")"),"("),HSTACK(SUBSTITUTE(A2:A5," ("&X&")","",1),X))

---

2) Through VBA:

Sub Test()

Dim arr As Variant: arr = Array("Ann Smith (A123456)", "Tom Ford(2453234)", "Alex Mohammet(4447434)(Text)", "Gerard Kowalski(A6739263)")

With CreateObject("vbscript.regexp")
    .Pattern = "^(.+?)\s*\(([A-Z]?\d+)\)(.*)$"
    For Each el In arr
        tmp = .Replace(el, "$1$3|$2")
        Debug.Print Split(tmp, "|")(0) 'Print name
        Debug.Print Split(tmp, "|")(1) 'Print ID
    Next
End With

End Sub

For those interested in a breakdown of the regular expression used, follow this link.

---

Another option with VBA is to use Split(), for example:

Sub Test()

Dim arr As Variant: arr = Array("Ann Smith (A123456)", "Tom Ford (2453234)", "Alex Mohammet (4447434)(Text)", "Gerard Kowalski (A6739263)")
Dim tmp As String

For Each el In arr
    tmp = Split(Split(el, "(")(1), ")")(0)
    Debug.Print Application.Trim(Replace(el, "(" & tmp & ")", ""))  'Print Name
    Debug.Print tmp                                                 'Print ID
Next

End Sub

---

Both options would print: