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

Is it correct that you want to extract the content of the first bracket? — Ike, Apr 07 '22 at 08:03
Use Split with '(' as the split character, then split again on item (1) but with ')' as the split character. The I'd will be in item(0) — freeflow, Apr 07 '22 at 08:06

JvdV · Answer 1 · 2022-04-07T08:25:52.300

6

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:

edited Apr 07 '22 at 08:25

answered Apr 07 '22 at 08:19

JvdV

70,606
8
39
70

I've come up with a slightly different looking regex with exact same result, but you beat me with 2 solutions in one answer before I posted just the regex one :) Note: `LET()` is available in O365 or Excel2021 ! – Piemol Apr 07 '22 at 08:26
@Piemol, please can you share your pattern? I know my pattern could be more specific but it felt like this would suffice. Interested in your approach! – JvdV Apr 07 '22 at 08:28
`"(.*) \(([[A-z]?\d+)\)(.*)"` I didn't mind a lower letter in front of the ID, and only allow a single space before the id parenthese starts. So it boils down to the data to expect :) – Piemol Apr 07 '22 at 08:33
That's a bit of a faulty character range to be honest. Note that `A-z` would match [ascii table 65-122](https://www.rapidtables.com/code/text/ascii-table.html) including non-alphabetic characters. To ignore case-sensitivity either expand the character class `[A-Za-z]` or use the [inline modifier](https://stackoverflow.com/a/60192760/9758194) to ignore cases. But if it works it works right =) – JvdV Apr 07 '22 at 08:38
1

Sir, this you call `Two Cents` but I think this is `Masterpiece`, only one approach missing that is `Power Query`, otherwise you have shown all possible ways, this is just amazing, there is no alternative. – Mayukh Bhattacharya Apr 07 '22 at 14:34

score 4 · Answer 2 · answered Apr 07 '22 at 08:11

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.

score 3 · Accepted Answer · answered Apr 07 '22 at 09:30

3

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

answered Apr 07 '22 at 09:30

Foxfire And Burns And Burns

10,665
2
17
38

2

Please be aware that the value `Alex Mohammet etc` gets chopped to only `Alex Mohammet`. Otherwise, yes, `Split()` is definately a good option + – JvdV Apr 07 '22 at 09:47
Works perfectly! Thank you! Also - tried different solutions. Thanks to all of you:) – Monika Apr 07 '22 at 09:48
3

@JvdV I see what you mean. Not all records follow same pattern `Name (id)`. You are right in this case. I did not notice that exception! – Foxfire And Burns And Burns Apr 07 '22 at 09:51

Separating text from alphanumeric

3 Answers3