Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    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.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 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

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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. #4
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    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; 2015-04-22 at 12:09.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 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
    ENewbie.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    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:
    Attached Files Attached Files
    Last edited by Excelnewbie; 2015-04-22 at 15:55.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Newbie,

    I made some adjustments to give maximum value:
    1. Created a header row
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG

    Very creative!

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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. #10
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    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 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. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 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)
    ..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. #12
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    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.

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    ..let's not remember we were all newbies once.

    zeddy

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 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
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    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.

Page 1 of 2 12 LastLast

Posting Permissions

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