Phone Number Filtering

[asadnaveed2]

I am trying to sort special numbers in a long list of telephone
numbers (7 digits). For example:

a. Highlight yellow those numbers which have 3 consecutive digits (eg
2281555).
b. Highlightt blue those numbers which have two double numbers (eg
4431122).
c. Highlight green those numbers which are in a certain sequence (eg
2281234).

I tried to use Left, Right and Mid formulas but the game becomes very
complicated and hard to troubleshoot. Any better solution?

 

[Dennis Tucker]

The only effective way I know how to do this, is to use macros.

Your samples a & b, are fairly simple to do. Sample c, could be difficult
depending on the your definition of "certain sequences".

Dennis

 

[muddan madhu]

assumed Col A has phone number.

Try this

Sub colr()
Dim rng As Integer, i As Integer, j As Integer
Dim qlen As Integer

rng = Cells(Rows.Count, "A").End(xlUp).Row

For j = 1 To rng
Cells(j, "A").Select
qact = ActiveCell.Value
qlen = Len(qact)

For i = 1 To qlen - 1
qmid = Mid(qact, i, 1)
qmid1 = Mid(qact, i + 1, 1)
If qmid = qmid1 Then
t = 1
temp = temp + t
End If
Next i

For i = 1 To qlen - 2
qmid = Mid(qact, i, 1)
qmid1 = Mid(qact, i + 1, 1)
qmid2 = Mid(qact, i + 2, 1)

If qmid = qmid1 And qmid1 = qmid2 Then
t1 = 1
temp1 = temp1 + t1
End If
Next i

If temp1 >= 1 Then
ActiveCell.Interior.Color = 65535
ElseIf temp > temp1 Then
ActiveCell.Interior.Color = 15773696
Else
ActiveCell.Interior.Color = 255
End If
temp = 0
temp1 = 0
Next
End Sub

 

[Rick Rothstein]

Are you talking about those patterns existing **only** in the last 4 digits
as your examples show, or are the patterns allowed to exist anywhere within
the 7 digit telephone number? **IF** the patterns are restricted to the last
4 digits, then I think you can use these formula in your Conditional
Formatting to do what you want...

3 consecutive digits
==================
=OR(MID(A1,4,2)=MID(A1,5,2),MID(A1,5,2)=MID(A1,6,2))

2 double numbers
==================
=AND(MID(A1,4,1)=MID(A1,5,1),MID(A1,6,1)=RIGHT(A1))

4 numbers in sequence
==================
=AND(1+MID(A1,4,1)=--MID(A1,5,1),1+MID(A1,5,1)=--MID(A1,6,1),1+MID(A1,6,1)=--RIGHT(A1))

I assumed that you meant 4 numbers in sequence for the last one when you
said "numbers which are in a certain sequence".