VBA中如何基于x分隔符从混合字符串提取长宽数值计算打印面积

开发者阿泽

2026-10-3

解决方案

核心思路

优先匹配大小写不敏感的x两侧紧邻的整数/浮点数作为长宽，完全过滤x之前的无关数字
提取长宽后再从剩余内容中匹配末尾数字作为件数

正则规则说明

匹配长宽的正则表达式为：(\d+\.?\d*)\s*[xX]\s*(\d+\.?\d*)

捕获组1：x左侧的整数/浮点数，对应长度
\s*：兼容x左右存在的任意空格
[xX]：适配大小写两种x的写法
捕获组2：x右侧的整数/浮点数，对应宽度

VBA实现代码

Function ExtractPrintParams(ByVal inputStr As String) As Variant
    Dim regex As Object
    Dim match As Object
    Dim length As Double, width As Double, qty As Integer
    Dim remainingStr As String
    
    ' 正则晚绑定，无需提前引用库
    Set regex = CreateObject("VBScript.RegExp")
    regex.Global = True
    regex.IgnoreCase = True
    
    ' 第一步：提取长宽
    regex.Pattern = "(\d+\.?\d*)\s*[xX]\s*(\d+\.?\d*)"
    If regex.Test(inputStr) Then
        Set match = regex.Execute(inputStr)(0)
        length = CDbl(match.SubMatches(0))
        width = CDbl(match.SubMatches(1))
        ' 移除匹配到的长宽部分，后续提取件数
        remainingStr = regex.Replace(inputStr, "")
    Else
        ' 无x匹配的场景可自定义默认返回值
        ExtractPrintParams = Array(0, 0, 0)
        Exit Function
    End If
    
    ' 第二步：提取件数，匹配剩余内容末尾的数字
    regex.Pattern = "[- ]+(\d+)\D*$"
    If regex.Test(remainingStr) Then
        Set match = regex.Execute(remainingStr)(0)
        qty = CInt(match.SubMatches(0))
    Else
        qty = 1 ' 未匹配到件数默认1件
    End If
    
    ExtractPrintParams = Array(length, width, qty)
End Function