如何使用Excel正则表达式从字符串中提取广告尺寸

问题描述

我正在尝试从字符串中提取广告尺寸.广告尺寸均为已设置的标准尺寸.因此，尽管我更希望有一个正则表达式来查找模式，即IE 3数字后跟2或3数字，但由于我们知道尺寸大小，因此也可以对其进行硬编码.以下是一些广告尺寸的示例:

I am trying to extract ad sizes from string. The ad sizes are all set standard sizes. So while I'd prefer to have a regex that looks for a pattern, IE 3 numbers followed by 2 or 3 numbers, hard coding it will also work, since we know what the sizes will be. Here's an example of some of the ad sizes:

300x250

728x90

320x50

我能够找到一些经过修改的VBScript几乎可以正常工作，但是由于我搜索的字符串不一致，因此在某些情况下它拉得太多了.例如:

I was able to find some VBScript that I modified that almost works, but because my strings that I'm searching are inconsistent, it's pulling too much in some cases. For example:

您会看到它在每个实例中都不正确匹配.

You see how it's not matching correctly in every instance.

我发现的VB代码实际上与所有广告尺寸都匹配.我对VBScript知之甚少，因此无法将其反转以仅查找广告尺寸并提取它们.因此，它会查找所有其他文本并将其删除.

The VB code I found is actually matching everything EXCEPT that ad sizes. I don't know enough about VBScript to reverse it to just look for ad sizes and pull them. So instead it looks for all other text and removes it.

代码在下面.有没有办法修复正则表达式，使其只返回广告尺寸?

The code is below. Is there a way to fix the Regex so that it just returns the ad sizes?

Function getAdSize(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "([^300x250|728x90])"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = True
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            getAdSize = regEx.Replace(strInput, strReplace)
        Else
            getAdSize = "Not matched"
        End If
    End If
End Function

请注意，数据并非总是由下级存储的，有时是前后的一个破折号或一个空格.

NOTE, THE DATA IS NOT ALWAYS PRECEDED BY AN UNDERSCORE, SOMETIMES IT IS A DASH OR A SPACE BEFORE AND AFTER.

推荐答案

编辑:由于实际上不是下划线分隔，所以我们不能使用 Split .但是，我们可以遍历字符串并手动提取" #x#".我已经更新了代码以反映这一点，并验证了它可以成功工作.

Since it's not actually underscore delimited we can't use Split. We can however iterate over the string and extract the "#x#" manually. I have updated the code to reflect this and verified that it works successfully.

Public Function ExtractAdSize(ByVal arg_Text As String) As String

    Dim i As Long
    Dim Temp As String
    Dim Ad As String

    If arg_Text Like "*#x#*" Then
        For i = 1 To Len(arg_Text) + 1
            Temp = Mid(arg_Text & " ", i, 1)
            If IsNumeric(Temp) Then
                Ad = Ad & Temp
            Else
                If Temp = "x" Then
                    Ad = Ad & Temp
                Else
                    If Ad Like "*#x#*" Then
                        ExtractAdSize = Ad
                        Exit Function
                    Else
                        Ad = vbNullString
                    End If
                End If
            End If
        Next i
    End If

End Function

使用Select Case布尔逻辑而不是嵌套的If语句的同一功能的替代版本:

Alternate version of the same function using Select Case boolean logic instead of nested If statements:

Public Function ExtractAdSize(ByVal arg_Text As String) As String

    Dim i As Long
    Dim Temp As String
    Dim Ad As String

    If arg_Text Like "*#x#*" Then
        For i = 1 To Len(arg_Text) + 1
            Temp = Mid(arg_Text & " ", i, 1)

            Select Case Abs(IsNumeric(Temp)) + Abs((Temp = "x")) * 2 + Abs((Ad Like "*#x#*")) * 4
                Case 0: Ad = vbNullString       'Temp is not a number, not an "x", and Ad is not valid
                Case 1, 2, 5: Ad = Ad & Temp    'Temp is a number or an "x"
                Case 4, 6: ExtractAdSize = Ad   'Temp is not a number, Ad is valid
                           Exit Function
            End Select
        Next i
    End If

End Function

这篇关于如何使用Excel正则表达式从字符串中提取广告尺寸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！