Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to locate several possible combinations of numbers within a string of text. The string of text could be any length and the numbers can start at any position within the string. The numbers I'm looking for can be any number and be 9 digits in length, 10 digits, 11 digits, 12 digits or 16 digits. These numbers may also have spaces or dashes within them at any place. Since there are so many possible combinations that could occur. I'm a little stumped on what options I could use. I've considered using Instr() as well as looking for values that are Like "######" (numbers) but not having any real luck with this. For anybody looking for a challenge, I could certainly use your help trying to solve this. Basically once I find a number combination in the string of text. I would just need to shade that cell to call it out. I'm currently using excel 2003 on Windows XP. Thanks to anybody offering up any suggestions.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Think you'll need to post an example
    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try this

    I have included "(" and ")" as not significant but you can take them out if I have guessed wrong.

    You need to select the cells to be tested and then run the sub.


    Option Explicit

    Sub colorPhonCells()
    Dim s As String
    Dim v As String
    Dim c As Range
    Dim n As Integer
    Dim j As Integer

    For Each c In Selection
    s = c.Text
    For j = 1 To Len(s)
    v = Mid(s, j, 1)
    Select Case v
    Case "0" To "9"
    n = n + 1
    If n > 8 Then c.Interior.Color = vbYellow
    Case "-", " ", "(", ")"
    Case Else
    n = 0
    End Select
    Next j

    Next c
    End Sub
    David Grugeon
    Brisbane Australia

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are the 9,10,11,12 and 16 digits excluding the spaces and dashes? What do you want done if there are numbers but they are a different number of digits? I suspect RegExp may be the best bet here.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew, Rory, David - Thank you all for your responses. I'm sure I could have included some more description in what I was performing this check against. Unfortunatly I couldn't post anything due to the sensitive nature of the data I'm working with and I didn't have any examples at the time to post. From David's response, I was able to work out a solution by slightly modifying what David suggested. Resetting the n counter when ever the v value was not a number space or dash. This allowed me to find a string of numbers, dashes and spaces with no letters in between. I think this should work. Below is what I have in case you find somebody else at a later time needs to perform a search of this type. Thanks again for your time and responses.

    By the way Rory. What did you mean by RegExp?? I'm not familiar with that.



    Option Explicit

    Sub colorPhonCells()
    Dim s As String
    Dim v As String
    Dim c As Range
    Dim n As Integer
    Dim j As Integer
    Dim r As Integer

    For r = 2 To 30000
    If Cells(r, 5).Value = "" Then Exit For
    Cells(r, 5).Select
    n = 0
    For Each c In Selection
    s = c.Text
    For j = 1 To Len(s)
    v = Mid(s, j, 1)
    If v Like "#" Or v Like " " Or v Like "-" Then
    Select Case v
    Case "0" To "9"
    n = n + 1
    If n > 8 Then c.Interior.Color = vbYellow
    Case "-", " "
    Case Else
    n = 0
    End Select
    Else
    n = 0
    End If
    Next j
    Next c
    Next r
    End Sub

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I was referring to Regular Expressions - see http://www.regular-expressions.info]this page[/url] for example.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    can you give us that URL again please

    zeddy

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I'll have a go


    After several attempts, I'm abandoning trying to do a live link since it gets converted into garbage. It's:
    www.regular-expressions.info
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    That one worked/

    many thanks

    zeddy

Posting Permissions

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