Results 1 to 9 of 9

20100623, 15:27 #1
 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.

20100623, 18:18 #2
 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

20100623, 20:52 #3
 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 SubDavid Grugeon
Brisbane Australia

20100624, 07:30 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,284
 Thanks
 3
 Thanked 193 Times in 179 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

20100624, 09:19 #5
 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

20100624, 09:26 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,284
 Thanks
 3
 Thanked 193 Times in 179 Posts
I was referring to Regular Expressions  see http://www.regularexpressions.info]this page[/url] for example.
Regards,
Rory
Microsoft MVP  Excel

20100626, 09:30 #7
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,941
 Thanks
 140
 Thanked 512 Times in 488 Posts
can you give us that URL again please
zeddy

20100626, 11:45 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,284
 Thanks
 3
 Thanked 193 Times in 179 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.regularexpressions.infoRegards,
Rory
Microsoft MVP  Excel

20100628, 09:35 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,941
 Thanks
 140
 Thanked 512 Times in 488 Posts
That one worked/
many thanks
zeddy