Find 7 digit number in string of text

Topic

New Reply

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.

Reply | Quote

Replies

Think you’ll need to post an example

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

I was referring to Regular Expressions – see http://www.regular-expressions.info]this page[/url] for example.

Reply | Quote

can you give us that URL again please

zeddy

Reply | Quote

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:
http://www.regular-expressions.info

Reply | Quote

That one worked/

many thanks

zeddy

Reply | Quote