VBA Separating text from numbers

Question

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

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

5

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

53,146
6
36
60

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

8,150
2
18
36

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

VBA Separating text from numbers

3 Answers3