使用正则表达式的多个if else [英] Multiple if else with Regex

问题描述

您好，



我有descr列，我需要针对多个字符串模式进行验证。我尝试在excel中使用元字符\ W和like。但我没有得到正确的输出。

Hello,

I have descr column which I need to validate against multiple string patterns. I tried using metacharacter "\W" with "like" in excel. But i did not get the correct output.

descr                   descr_NEW
white cat big                yes
small donkey black           no

Private Sub model()
Dim descr_NEW As String, descr As String
Dim strPattern As String: strPattern = "(\W|^)cat(\W|$)"
Dim regEx As New RegExp
row_number = 1

Do
DoEvents
row_number = row_number + 1
descr = Sheets("Tabelle1").Range("A" & row_number)
 
 With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = True
                .Pattern = strPattern
            End With

If descr Like "(\W|^)cat(\W|$)" Then
descr_NEW = "yes"

Else
descr_NEW = "no"


'If regEx.test(descr) Then
'descr_NEW = "cat"
'
'Else
'descr_NEW = "no"


End If

Range("B" & row_number).Value = descr_NEW

Loop Until row_number = 10
End Sub

而不是喜欢，我尝试了regex.test（）（评论下的一段代码），它运行正常。但是我希望使用\ W和喜欢来实现结果。



有什么办法吗？



提前致谢

priya

Instead of "like", I tried regex.test() (piece of code under comments) and it works fine. But i want to acheive the result using "\W " with "like".

Is there any way?

Thanks in advance
priya

推荐答案

）
Dim regEx As New RegExp
row_number = 1

Do
DoEvents
row_number = row_number + 1
descr = Sheets（Tabelle1）。Range（A& row_number）

使用regEx
.Global = True
.MultiLine = True
.IgnoreCase = True
.Pattern = strPattern
以
$结尾b $ b如果descr喜欢（\W | ^）cat（\W |

)" Dim regEx As New RegExp row_number = 1 Do DoEvents row_number = row_number + 1 descr = Sheets("Tabelle1").Range("A" & row_number) With regEx .Global = True .MultiLine = True .IgnoreCase = True .Pattern = strPattern End With If descr Like "(\W|^)cat(\W|

）那么
descr_NEW =是

Else
descr_NEW =no


'如果regEx.test（descr）那么
'descr_NEW =cat
'
'Else
'descr_NEW =no


结束如果

范围（B& row_number）.Value = descr_NEW

Loop Until row_number = 10
End Sub

)" Then descr_NEW = "yes" Else descr_NEW = "no" 'If regEx.test(descr) Then 'descr_NEW = "cat" ' 'Else 'descr_NEW = "no" End If Range("B" & row_number).Value = descr_NEW Loop Until row_number = 10 End Sub

而不是喜欢，我尝试了regex.test（）（评论下的一段代码），它运行正常。但是我希望使用\ W和喜欢来实现结果。



有什么办法吗？



提前致谢

priya

Instead of "like", I tried regex.test() (piece of code under comments) and it works fine. But i want to acheive the result using "\W " with "like".

Is there any way?

Thanks in advance
priya

您不需要任何VBA代码！使用内置公式：

You don't need any VBA code for that! Use built-in formula:

=IF(ISNUMBER(FIND("cat",A2))>0,"yes","no")

A2 - 是对 descr 数据的引用。



如果你想要使用VBA，你需要循环遍历单元格集合：

A2 - is a reference to descr data.

If you would like to use VBA, you need to loop through the collection of cells:

Sub Test
Dim wsh As Worksheet
Dim i As Integer

Set wsh = Thisworkbook.Worksheets("Sheet1")
i = 2
Do While wsh.Range("A" & i)<>""
    wsh.Range("B" & i) = Test4Cat(wsh.Range("A" & i))
    i = i +1
Loop

End Sub

Function Test4Cat(ByVal sInput As String)

    Test4Cat = IIf(InStr(1, LCase(sInput), "cat", vbTextCompare) > 0, "yes", "no")

End Function

使用与Regex相同的逻辑;）







关于答案的评论......你必须创建自定义功能来验证数据是否符合你的需要。

Use the same logic with Regex ;)



As concerns comments to the answer... you have to create custom function to validate if data meets your needs or no.

Function cfValidate(ByVal sDescription As String) As String
Dim vPatterns As Variant
Dim i as Integer
Dim retVal as String

retVal = "yes" 'return default value

vPatterns = Array("pattern1","pattern2","pattern3")

For i=LBound(vPatterns) To UBound(vPatterns)
    'here call Regex
    'if Regex found matches, the string passed into it is invalid
    retVal = "no"
Next i

cfValidate = retVal
End Function

注意： vPatterns 是用于检查字符串是否有效的模式的集合（数组）。



您可以使用以上函数作为公式：

Note: vPatterns is a "collection" (array) of patterns to check if string is valid.

You can use above function as formula:

=cfValidate(A2)

[/ EDIT]

[/EDIT]

这篇关于使用正则表达式的多个if else的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！