求助：用VBA从字符串中提取数字

cj04psh

TB-SL-1130-450
上面字符串我需要提取1130 跟450 两个字符串出来分别存储为数字，长度会有变化，怎样用VBA实现，求助，谢谢！

不知道为什么

用Split 函数把
ms="TB-SL-1130-450"
ms1=split(ms,"-")(2)
ms2=split(ms,"-")(3)

一把小刀闯天下

'自己修改

Option Explicit

Sub test()
  Dim t, i
  t = Split("TB -SL - 1130 - 450", "-")
  For i = 0 To UBound(t)
    If Len(t(i)) > 0 And IsNumeric(t(i)) Then Debug.Print t(i)
  Next
End Sub

jokklx

split("TB-SL-1130-450","-")(2)
结果是1130
split("TB-SL-1130-450","-")(3)
结果是450

cj04psh

谢谢两位快速回复！我自己试下改改。

lsdongjh

正则，

1. Sub Test()
   
2.     Dim objReg As Object, strTemp As String, strPat As String
   
3.     Dim objMatchs As Object, objMatch As Object
   
4.     Dim intID As Integer
   
5. 
   
6.     strTemp = "TB-SL-1130-450"
   
7. 
   
8.     strPat = "\d+"
   
9. 
   
10.     Set objReg = CreateObject("VBScript.RegExp")
    
11.     With objReg
    
12.         .Global = True
    
13.         .Pattern = strPat
    
14.     End With
    
15.    
    
16.     Set objMatchs = objReg.Execute(strTemp)
    
17. 
    
18.     For Each objMatch In objMatchs
    
19.         intID = intID + 1
    
20.         MsgBox "第" & intID & "个数字为：" & objMatch
    
21.     Next
    
22. End Sub

复制代码

LMY123

正则提取数字

cj04psh

用了2楼建议的函数搞定了，么想到这么简单啊，谢谢大家！后面的代码我也抽空学习下！

kuangben8

=--MID(substitute(A1,"-",REPT(" ",99)),3*99-98,99)

=--MID(substitute(A1,"-",REPT(" ",99)),4*99-98,99)

獨一無2

Sub 提取()
n = 2
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "-(\d+)"
    Set mat = .Execute(Sheet2.[a1])
    For Each m In .Execute(Sheet2.[a1])
        Sheet2.Cells(1, n) = .Replace(m, "$1")
        n = n + 1
    Next
End With
End Sub