Separating text from alphanumeric

user_18733799 0
Viewed 115

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?

enter image description here

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
vba excel
3 Answers

My two cents.

1): Through formulae:

enter image description here

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:

enter image description here

user_97581940

You can do this with a formula:

Name-column: =MID([@worker],1,FIND("(", [@worker])-1)

ID-column: =MID([@worker],FIND("(",[@worker])+1,FIND(")",[@worker])-FIND("(",[@worker])-1)

enter image description here

If you are on the Beta-Channel of excel 365 than you might already have TEXTSPLIT and TEXTBEFORE.

user_165784240

enter image description here

Sub NamesandID()

Dim RowNum As Long

RowNum = 2
Do Until Cells(RowNum, 1).Value = ""
    'f you need parenthesis in the name concatenate them at the end, something like below
    'Range("B" & RowNum).Value = Split(Range("A" & RowNum), " (")(0) & " ()"
    Range("B" & RowNum).Value = Split(Range("A" & RowNum), " (")(0) 'no parenthesis at the end
    Range("C" & RowNum).Value = Split(Split(Range("A" & RowNum), " (")(1), ")")(0)

RowNum = RowNum + 1
Loop
End Sub
user_91998280
Related