检查Excel单元格中的连续字符

If you could help me I am in need to finding out if a character of the alphabet repeats consecutively 3 or more times in a cell, eg if a cell is "aronfff" or "aaaaaron" I want it to return true otherwise to return false eg "aaron".

如果你可以帮助我,我需要找出一个字母表中的字符是否在一个单元格中连续重复3次或更多次,例如,如果一个单元格是“aronfff”或“aaaaaron”我希望它返回true,否则返回false例如“aaron”。

Function InRowChars(cell As String) As Boolean
Dim repeats As Integer, char As String, i As Integer
repeats = 0
char = "abcdefghijklmnopqrstuvwxyz"

For i = 1 To Len(cell)
    If cell.Value = " " Then
        repeats = chars + 1
    Else
        chars = 0
    End If
Next i

If chars = 3 Then
    InRowChars = True
Else
    InRowChars = False
End If

End Function

I don't know how to get the value of the cell to be checked against the alphabet.

我不知道如何根据字母表获取要检查的单元格的值。

4 个解决方案

#1

Here is a another regex solution that returns TRUE or FALSE depending on whether or not there are three or more repeating alphabetic characters:

这是另一个正则表达式解决方案,它返回TRUE或FALSE,具体取决于是否有三个或更多重复的字母字符:

Option Explicit
Function TripleChars(S As String) As Boolean
    Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "([a-z])\1\1"
    .ignorecase = True 'edit as desired
    TripleChars = .test(S)
End With
End Function

And here is an explanation of the Regex Pattern:

以下是正则表达式的解释:

([a-z])\1\1

([a-z])\1\1

Options: Case insensitive; ^$ don’t match at line breaks

选项:不区分大小写; ^ $不匹配的换行符

• Match the regex below and capture its match into backreference number 1 ([a-z])
  • Match a single character in the range between “a” and “z” [a-z]

  匹配“a”和“z”[a-z]之间范围内的单个字符

匹配下面的正则表达式并将其匹配捕获到反向引用号1([a-z])匹配“a”和“z”[a-z]之间范围内的单个字符

• Match the same text that was most recently matched by capturing group number 1 \1

通过捕获组编号1 \ 1匹配最近匹配的相同文本

• Match the same text that was most recently matched by capturing group number 1 \1

通过捕获组编号1 \ 1匹配最近匹配的相同文本

Created with RegexBuddy

使用RegexBuddy创建

#2

This can be achieved with regular expressions. I've made a function example that also accept the number of minimum characters desired:

这可以通过正则表达式实现。我做了一个函数示例,也接受了所需的最小字符数:

'Add a reference to Microsoft VBScript Regular Expressions 5.5
Function ContainsConsecutiveChars(ByRef CellRef As Range, Optional ConsecutiveCount As Long = 3) As Boolean

  Dim chars() As String
  chars = Split("a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z", ",")

  With New RegExp
    .Pattern = Join(chars, "{" & ConsecutiveCount & ",}|")
    ContainsConsecutiveChars = .test(CellRef.Value2)
  End With

End Function

#3

I see you already have a RegEx answer now. Just finished my version so thought I'd post it to.
@Thunderframe - I liked the optional bit, so have blatantly taken it for my version to.

我看到你现在已经有了RegEx的答案。刚刚完成我的版本,所以我想发布它。 @Thunderframe - 我喜欢可选位,因此我公然将它用于我的版本。

Public Function RepeatingChars(Target As Range, Optional ConsecutiveCount As Long = 3) As Variant

    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("VBScript.RegExp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "(.)\1{" & ConsecutiveCount - 1 & ",}"
    End With

    Set REMatches = RE.Execute(Target.Value)
    If REMatches.Count = 0 Then
        RepeatingChars = CVErr(xlErrNA)
    Else
        RepeatingChars = REMatches(0)
    End If

End Function

The function will return duplicates of any character, or #NA if no matches found.

该函数将返回任何字符的重复项,如果未找到匹配项,则返回#NA。

Edit
After a quick re-read of your question you can replace the whole If...End If block with RepeatingChars = REMatches.Count <> 0 to return TRUE/FALSE. Remember to change the return type of the function to Boolean in this case.

编辑快速重新阅读您的问题后,您可以使用RepeatingChars = REMatches.Count <> 0替换整个If ... End If块以返回TRUE / FALSE。在这种情况下,请记住将函数的返回类型更改为布尔值。

#4

This is what I have came up with so far:

这是我到目前为止所提出的:

Option Explicit

Function checkChars(inputCell As String, Optional repeat As Long = 3) As Boolean

    Dim cnt                 As Long
    Dim previous            As String
    Dim countResult         As Long

    For cnt = 1 To Len(inputCell)

        If previous = Mid(inputCell, cnt, 1) Then
            countResult = countResult + 1                
        Else
            countResult = 1
        End If

        If countResult = (repeat) Then
            checkChars = True
            Exit Function
        End If

        previous = Mid(inputCell, cnt, 1)
    Next cnt

End Function

Public Sub TestMe()

    Debug.Print checkChars("lalaaa")
    Debug.Print checkChars("lalaala", 2)
    Debug.Print checkChars("lalaala", 1)
    Debug.Print checkChars("lflalajajala", 2)

End Sub

The idea is that you can also pass the repeat number as an optional value, if it is different than 3. This is what you get as an output from the example:

我们的想法是,如果重复数不同于3,您也可以将重复数作为可选值传递。这是您从示例中获得的输出:

True
True
True
False