# Thread: Find 7 digit number in string of text

1. 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. Think you'll need to post an example

3. 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

4. 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.

5. 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. I was referring to Regular Expressions - see http://www.regular-expressions.info]this page[/url] for example.

7. can you give us that URL again please

zeddy

8. 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

9. 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
•