If cell contains string of letter or number repetition vba

[borgercity]

Hi,
I need a formula which makes it possible to find string of letter or number repetition in a specific colomn such as:
- ‘XXXXXX’ or ‘ABCDEF’
- ‘123456’ or ‘111111’

Thank you in advance!

 

[Shortmeister1]

Something like this?

=IF((IFERROR(FIND("XXXXXX",A5),0)+IFERROR(FIND("ABCDEF",A5),0)+IFERROR(FIND("123456",A5),0)+IFERROR(FIND("111111",A5),0))>0,TRUE,FALSE)

 

[borgercity]

Something like this?

=IF((IFERROR(FIND("XXXXXX",A5),0)+IFERROR(FIND("ABCDEF",A5),0)+IFERROR(FIND("123456",A5),0)+IFERROR(FIND("111111",A5),0))>0,TRUE,FALSE)

Thanks but that's not what I'm looking. The list is non-exhaustive. Those I have mentioned are just a few examples I don't know if it's possible to have something in VBA for that matter

 

[Joe4]

I think you may need to explain in more detail.
I can see how "XXXXXX" or "111111" is repetition, but how are you considering "ABCDEF" or "123456" repetition?

Also, how long are these strings, and how many consecutive characters do you need for it to be considered?
What if you have things like?
"AAACKS" or "111156"?

I also have a feeling that you may be oversimplifying your examples.
Can you show us a much larger data set that is actually representative of your real data, and then show us your expected results from that data (you also haven't told us what you want to do with this data when found)?

 

[borgercity]

I think you may need to explain in more detail.
I can see how "XXXXXX" or "111111" is repetition, but how are you considering "ABCDEF" or "123456" repetition?

Also, how long are these strings, and how many consecutive characters do you need for it to be considered?
What if you have things like?
"AAACKS" or "111156"?

I also have a feeling that you may be oversimplifying your examples.
Can you show us a much larger data set that is actually representative of your real data, and then show us your expected results from that data (you also haven't told us what you want to do with this data when found)?

I apologize for my unclear explanation. It concerns a list of customers that I cannot simply place here. Suppose column A contains all the names of the customers. Then the intention is to check whether the information is not unclear. Unclear means, among other things, repetition of letters and numbers. Suppose a customer passes on "AABC" as his name, and in the case of the character of 2 successive repetitions, this should give an indication for further checking (let us forget "12345" and "ACBDE").

 

[Joe4]

So, you are just trying to look for patterns that do not look like names?
If so, looking for numbers or symbols is simple enough, but letter patterns can get a lot trickier, because how can you tell what is a name and what isn't?
A bunch of names have repeating letters, i.e.
Aaron
Miller
Reed

 

[Shortmeister1]

Is it possible to tackle this a different way and start with a "definitive" approved customer list? It would then be a matter of comparing your table against the control list and approving all new approved entries as a one off task, by adding them to said control list.

 

[Peter_SSs]

in the case of the character of 2 successive repetitions, this should give an indication for further checking

If that is in fact what you want, then you could try this macro.

Sub RepeatLetters()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "(.)(?=\1)"
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 1)
    b(1, 1) = "Repeat Characters"
    For i = 2 To UBound(a)
      If RX.Test(a(i, 1)) Then b(i, 1) = RX.Execute(a(i, 1)).Count
    Next i
    .Offset(, 1).Value = b
  End With
End Sub

My sample data and results.
This code checks for any repeat characters, not just letters. For example, there are no repeat letters in row 10 but there is a double space between the two names.

borgercity.xlsm
	A	B
1	Names	Repeat Characters
2	AABC	1
3	Jones
4	Jjones	1
5
6	Peters
7	Reed	1
8	Llewwellyn	3
9	AAABC	2
10	Ken Smith	1
Sheet1

If you wanted a version without using regular expressions (perhaps it needs to also work on a Mac) then this produces the same results.

Sub RepeatLettersB()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim s As String
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 1)
    b(1, 1) = "Repeat Characters"
    For i = 2 To UBound(a)
      s = LCase(a(i, 1))
      k = 0
      For j = 2 To Len(s)
        If Mid(s, j, 1) = Mid(s, j - 1, 1) Then k = k + 1
      Next j
      If k > 0 Then b(i, 1) = k
    Next i
    .Offset(, 1).Value = b
  End With
End Sub