时间戳

首页 > 解决方案 > 使用 VBA 将字符串分成“名称”和“数字”两部分

vba - 使用 VBA 将字符串分成“名称”和“数字”两部分

问题描述

我需要将以下内容strings分为名称和编号：例如

evil333 成 恶 333

bili454 变成 bili 和 454

elvis04 变成 elvis 和 04

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

相似地

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

那么最好的开始方式应该是什么？只想让它尽可能简单

更新： 有关更多信息，请关注 - https://youtu.be/zjF7oLLgtms

标签： vbasplitms-word

解决方案

解决这个问题的另外两种方法：

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

或者另一种方法：

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

推荐阅读