Results 1 to 15 of 21

20150420, 20:25 #1
 Join Date
 Apr 2015
 Posts
 158
 Thanks
 72
 Thanked 0 Times in 0 Posts
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.

20150420, 21:04 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,491
 Thanks
 377
 Thanked 1,473 Times in 1,340 Posts
Newbie,
We can write the code if you can write the specifications! I think you'll find the latter is the harder.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150421, 06:27 #3
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 519 Times in 495 Posts
Perhaps another way would be to eliminate all the 'hardtoremember' phone numbers.
Then you would be left with the easy ones.
zeddy

20150422, 11:12 #4
 Join Date
 Apr 2015
 Posts
 158
 Thanks
 72
 Thanked 0 Times in 0 Posts
RG and Zeddy
Thanks for your reply.
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.
I searched google and didn't find anything that was very helpful.Last edited by Excelnewbie; 20150422 at 12:09.

20150422, 12:14 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,491
 Thanks
 377
 Thanked 1,473 Times in 1,340 Posts
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
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150422, 15:38 #6
 Join Date
 Apr 2015
 Posts
 158
 Thanks
 72
 Thanked 0 Times in 0 Posts
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:Last edited by Excelnewbie; 20150422 at 15:55.

20150422, 18:20 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,491
 Thanks
 377
 Thanked 1,473 Times in 1,340 Posts
Newbie,
I made some adjustments to give maximum value:
 Created a header row
 Created conditional formatting to highlight your best choices.
 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
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150422, 18:53 #8
 Join Date
 Apr 2015
 Posts
 158
 Thanks
 72
 Thanked 0 Times in 0 Posts
RG
Very creative!

20150423, 11:25 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 519 Times in 495 Posts
Hi Excelnewbie
It's hard to understand what an 'easytoremember' 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 phonepad, so you can have things like Dial 1800Food, Dial 1800cars etc etc
zeddy

20150423, 12:16 #10
 Join Date
 Apr 2015
 Posts
 158
 Thanks
 72
 Thanked 0 Times in 0 Posts
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 2545550, 2545000, 5555500.
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 'hardtoremember' phone numbers.
Then you would be left with the easy ones."

20150423, 13:16 #11
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 519 Times in 495 Posts
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)
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

20150423, 13:45 #12
 Join Date
 Apr 2015
 Posts
 158
 Thanks
 72
 Thanked 0 Times in 0 Posts
Zeddy
I'm glad I chose the name Excelnewbie. It makes something like this a little easier to deal with.

20150423, 13:53 #13
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 519 Times in 495 Posts
..let's not remember we were all newbies once.
zeddy

20150423, 13:58 #14
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,491
 Thanks
 377
 Thanked 1,473 Times in 1,340 Posts
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
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.
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150423, 18:11 #15
 Join Date
 Apr 2015
 Posts
 158
 Thanks
 72
 Thanked 0 Times in 0 Posts
RG
This is perfect. Works great. Thank you!
Now my task is to understand, at least in part, some of the code.