Separate a String into Two parts 'name' and 'number' using VBA

Question

I need to separate following strings into Name and Number: e.g.

evil333 into evil and 333

bili454 into bili and 454

elvis04 into elvis and 04

Split(String, "#") ' don't work here because numbers are unknown

similarly

Mid(String, 1, String - #) ' don't work because Numbers length is unknown

so what should be the best way to start? Just want to keep it simple as possible

Update: For further info follow - https://youtu.be/zjF7oLLgtms

Loop thru each character until you find the first numeric digit, and then use that index to break it into 2 pieces. — braX, Dec 13 '19 at 10:37
[For your reference](https://stackoverflow.com/questions/3547744/vba-how-to-find-position-of-first-digit-in-string) to go with @braX comment. — Plutian, Dec 13 '19 at 10:45
Or use [Regular Expressions](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) (link is for Excel but works the same in Word) and the following pattern [`^([^0-9]*)([0-9]*)$`](https://regex101.com/r/qKqdJy/1) to split it. — Pᴇʜ, Dec 13 '19 at 10:48

Oleksandr Krasnoshchok · Answer 1 · 2019-12-13T11:23:31.397

Two more ways for solving this:

Sub test()

Dim sInputString As String
Dim i As Integer
Dim lFirstNumberPos As Long

sInputString = "evil333"
'loop through text in input string
'if value IsNumeric (digit), stop looping
For i = 1 To Len(sInputString)
    If IsNumeric(Mid(sInputString, i, 1)) Then
        lFirstNumberPos = i
        Exit For
    End If
Next i

Dim Name As String
Dim Number As String

'return result
Name = Left$(sInputString, lFirstNumberPos - 1)
Number = Mid$(sInputString, lFirstNumberPos)

End Sub

Or another method:

Sub test2()

'if you are going to have too long string it would maybe better to use "instr" method
Dim sInputString As String
Dim lFirstNumberPos As Long
Dim i As Integer

sInputString = "evil333"

Dim lLoopedNumber as Long
LoopedNumber = 0
lFirstNumberPos = Len(sInputString) + 1
'loop through digits 0-9 and stop when any of the digits will be found
For i = 0 To 9
    LoopedNumber = InStr(1, sInputString, cstr(i), vbTextCompare)
    If LoopedNumber > 0 Then
        lFirstNumberPos  = Application.Min(LoopedNumber,lFirstNumberPos)
    End If
Next i

Dim Name As String
Dim Number As String

'return result
Name = Left$(sInputString, lFirstNumberPos - 1)
Number = Mid$(sInputString, lFirstNumberPos)


End Sub

score 0 · Answer 2 · answered Dec 13 '19 at 13:44

You should regular expressions (regex) to match the two parts of your strings. The following regex describes how to match the two parts:

/([a-z]+)([0-9]+)/

Their use in VBA is thorougly explained in Portland Runner's answer to How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Separate a String into Two parts 'name' and 'number' using VBA

2 Answers2