CODEWITHSUNDEEP
vbasplitms-word
VBAbyMBA
VBAbyMBA

Reputation: 826

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

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

Upvotes: 0

Views: 1871

Answers (2)

Pierre Thierry
Pierre Thierry

Reputation: 5129

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

Upvotes: 0

Oleksandr Krasnoshchok
Oleksandr Krasnoshchok

Reputation: 113

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

Upvotes: 2

Related Questions