# Thread: Using Excel to find easy to remember phone numbers in a long list of numbers

1. ## Using Excel to find easy to remember phone numbers in a long list of numbers

Is there a way to find phone phone numbers that are easy to remember in a long list of numbers? I search Google and didn't find much help.

The phone numbers do not contain "-". They are a string of 10 numbers. Like this 8558975402.

I think an easy to remember phone number would have numbers that repeat, especially the last 4 numbers. Something like 4545, 5500, and etc. However, I would like to check all 10 numbers if possible.

I don't know of a formula in Excel that will do this. It may take VBA.

Thanks in advance for any help.

2. Newbie,

We can write the code if you can write the specifications! I think you'll find the latter is the harder.

3. Perhaps another way would be to eliminate all the 'hard-to-remember' phone numbers.
Then you would be left with the easy ones.

zeddy

4. RG and Zeddy-

Is there a formula or vba code that would identify repeating numbers in list. For example, if I wanted to ID how many times the number 5 appears in a cell containing 10 numbers.

5. Newbie,

Here's code to do it.
Code:
```Option Explicit

Function iCountDigit(zSearchStr As String, zDigit As String) As Integer

'*** Note: this will work if the Excel Value is a Number or String as it
'***       will cast a Number to String

Dim iCntr   As Integer
Dim iLen    As Integer

iLen = Len(zSearchStr)
iCountDigit = 0
iCntr = 1

Do
iCntr = InStr(iCntr, zSearchStr, zDigit, vbTextCompare)
If iCntr > 0 Then
iCountDigit = iCountDigit + 1
iCntr = iCntr + 1
End If

Loop While iCntr > 0

End Function   'iCountDigit```
ENewbie.JPG

HTH

6. RG

Edit:

I just saw what you added at the bottom. Thanks

Amazing. Thank you for the code.

I'm moving in baby steps learning vba.

I've attached a file with 50, 7 digit phone numbers (I removed the area code). I opened a module and entered the code you provided. Unfortunately, at my present skill level
I'm not sure how to make the code work. I pressed F5 and all it did was bring up a Macro Window.

What am I missing?

Thanks again!

Note:

7. Newbie,

2. Created conditional formatting to highlight your best choices.
3. Froze line 1 on screen for scroling

So that done I entered this formula in B2: =iCountDigit(\$A2,B\$1)
Then I filled it right to column K.
Then I filled down to row 51.
ENewbie.JPG
Note: More than 3 matches will show up in RED!

Test File: Find Repeating Number.xlsm

HTH

8. RG

Very creative!

9. Hi Excelnewbie

It's hard to understand what an 'easy-to-remember' number 'means'.
For example, in mathematics, the irony is that the 'more random' you make a number, the more unique it becomes!
For phone numbers, you can use 'words' based on the letters associated on a phone-pad, so you can have things like Dial 1-800-Food, Dial 1-800-cars etc etc

zeddy

10. Hi Zeddy-

I agree with you. I'm approaching this multiple faceted subject using repeating numbers. With the VBA code RetiredGeek provided I was able to make some progress.

What I would like to do next is find code like RG provided that would find sequential repeating numbers. That would provide a method that would search out numbers like 254-5550, 254-5000, 555-5500.

Attached is a file I'm working on.

I found what you said the other day to be accurate, "Perhaps another way would be to eliminate all the 'hard-to-remember' phone numbers.
Then you would be left with the easy ones."

11. Hi Excelnewbie

You formula in column [L] misses column [B], and, if corrected, will always give the result of 7 (if you think about it). I would recommend changing the col [L] formula to
Code:
`=max(b3:k3)`
..and then copy down.
Now, the higher this number, the more repeating digits found.
After entering this formula, look down (or use Filter) and see the number of results that say, are 3.
Look at the corresponding number in col [A].
Perhaps the col [O] should be Yes if col [L] > 2 ?????

zeddy

12. Zeddy-

I'm glad I chose the name Excelnewbie. It makes something like this a little easier to deal with.

13. ..let's not remember we were all newbies once.

zeddy

14. Newbie,

Ok, here's a modification that will return the number of consecutive repetitions of a number equal to or over a specified threshold.

Code:
```Function iCountReptDigit(zSearchStr As String, zDigit As String, iRpt As Integer) As Integer

'*** Note: this will work if the Excel Value is a Number or String as it
'***       will cast a Number to String

Dim iCntr     As Integer
Dim iLastFind As Integer
Dim iRptCntr  As Integer
Dim iStrPtr   As Integer
Dim iLen      As Integer
Dim bFound    As Boolean

iLen = Len(zSearchStr)
iRptCntr = 0
iStrPtr = 1
bFound = False

Do
iCntr = InStr(iStrPtr, zSearchStr, zDigit, vbTextCompare)
If iCntr > 0 Then
Select Case True
Case iRptCntr = 0                      '*** First Match
bFound = True
iRptCntr = 1
Case bFound And iCntr = iLastFind + 1  '*** Consecutive Match
iRptCntr = iRptCntr + 1
Case Else                              '*** Not consecutive Match
iRptCntr = 1
End Select

iLastFind = iCntr
iStrPtr = iCntr + 1

End If

Loop Until iCntr = 0

iCountReptDigit = IIf(iRptCntr >= iRpt, iRptCntr, 0)

End Function   'iCountDigit```
ENewbie.JPG

Test File: Find Repeating Number.xlsm

Note: The new macro version is used on Sheet2!

Note2: Notice use of NamedRange so you can change the repetition threshold w/o redoing the formulas.

HTH

15. RG-

This is perfect. Works great. Thank you!

Now my task is to understand, at least in part, some of the code.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•