VBA Separating text from numbers-CodePudding

I had to quickly separate text from numbers, but there are some exceptions. Actually it is about separating names from IDs. Code separates text from numbers totally fine, but some of the IDs have got a letter at the beginning. So the problem begins at this place. What has to be added and changed in the code in order to obtain full ID with a letter (if applicable)?

Thanks for help!

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

CodePudding user response：

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

CodePudding user response：

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)

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