VBA SPLIT由不同的分隔符 [英] VBA SPLIT by different delimiter

问题描述

我有一个很棘手的问题.我试图拆分并声明字符串的其他部分以备将来使用.显然，我有一个不同的定界符来做到这一点.

I have a rather tricky problem. I am trying to split and declare a different parts of a string for further use. Obviously there I have a different delimiters to do that.

说我想将标准的螺钉代码DIN912M6x10A2分成不同的部分，因为该代码的每个部分都具有特定的含义.

Say I wanted to split a standard screw code: DIN912M6x10A2 into it's different parts since each part of that code means specific something.

ScreHead一直保留到第一个"M".没有定界符= DIN912

ScreHead is Left up to first "M" without the delimiter = DIN912

ScrewThickness为"M"；最多包含"x"个排除= M5

ScrewThickness is "M" included up to "x" excluded = M5

ScrewLenght为"x"，最多排除"A"排除= 10

ScrewLenght is "x" excluded up to "A" excluded = 10

螺丝材料为"A"，最多包含到""或如果没有""然后直到字符串的结尾= A2

ScrewMaterial is "A" included up to the " " or if there's no " " then up to the end of the string = A2

到目前为止，我的代码是(我正在第5栏工作):

What I have so far codewise is (I am working in 5th column):

Dim ScrewHead As Long
ScrewHead = Split(Cells(i, 5), "M"-1)

Dim ScrewDiameter As Long
ScrewDiameter =Split(i,5),"M", "x"-1)

Dim ScrewLenght As Long
ScrewLenght =Split(i,5),"x"-1, "A"-1)

Dim ScrewMaterial As Long
ScrewMaterial =Split(i,5),"A", " ")

有人可以帮我解决这个问题吗?

Could someone help me with figuring this one out?

推荐答案

当您可以捕获自己组中的所有部分时，听起来很不错，正则表达式很诚实.例如通过:

Sounds like a nice job for a regular expression to be honest when you can capture all the parts in their own groups. For example through:

^(.+?)(M\d+)x(\d+)(.+?)(?:\s.*)?$

请参见在线演示

• ^ -起始行锚点.
• (.+?)-第一个捕获组，最多保留1个(惰性)字符；
• (M \ d +)-具有文字"M"的第二个捕获组后跟1+(贪婪的)数字.
• x -文字"x".
• (\ d +)-包含1个(贪婪)数字的第三个捕获组.
• (.+?)-第四个捕获组，最多保留1个(惰性)字符；
• (?:\ s.*)?-可选的非捕获组，包含0+(贪婪)字符的空格字符.
• $ -结束行锚点.

• ^ - Start line anchor.
• (.+?) - A 1st capture group holding 1+ (lazy) characters upto;
• (M\d+) - 2nd Capture group with a literal "M" followed by 1+ (greedy) digits.
• x - A literal "x".
• (\d+) - A 3rd capture group holding 1+ (greedy) digits.
• (.+?) - A 4th capture group holding 1+ (lazy) characters upto;
• (?:\s.*)? - An optional non-capture group of a space character with 0+ (greedy) characters.
• $ - End line anchor.

这是运行以检索这些组的快速代码:

Here is a quick code to run to retrieve these groups:

Sub Test()

Dim str As String: str = "DIN912M6x10A2 test"
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "^(.+?)(M\d+)x(\d+)(.+?)(?:\s.*)?$"
    If .Test(str) = True Then
        For Each Match In .Execute(str)(0).Submatches
            Debug.Print Match
        Next
    End If
End With

End Sub

---

更广泛的代码示例，可以使您更好地理解:

---

A more extensive code-example for a better understanding:

Sub Test()

Dim str As String: str = "DIN912M6x10A2 test"
Dim ScrewHead As String, ScrewDiameter As String, ScrewLenght As Long, ScrewMaterial As String
Dim matches

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "^(.+?)(M\d+)x(\d+)(.+?)(?:\s.*)?$"
    If .Test(str) = True Then
        Set matches = .Execute(str)
        ScrewHead = matches(0).Submatches(0)
        ScrewDiameter = matches(0).Submatches(1)
        ScrewLenght = matches(0).Submatches(2)
        ScrewMaterial = matches(0).Submatches(3)
    End If
End With

End Sub

这篇关于VBA SPLIT由不同的分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！