Formula to identify cells that have 7 numbers with a string

[powercell99]

Looking for help, suggestions for a formula that would identify cells that contain a string (text string, like a sentence) but within the sentence there is a grouping of 7 numbers together. ex: cell value: "this item is for john in the mail room, 1234567". the 7 numbers could be at any character position, but they would be grouped together, because the cell is freehand and any number of people would enter the data.

the numbers could be any number or combination of numbers, but there would be 7 of them. There isnt a numerical wildcard character for excel? like: search(" #######", A1, 1)

Any suggestions or recommendations you could think of would be greatly appreciated.

Thanks

 

[cerfani]

not sure if there is a direct way to do it in excel but in vba the logic is easy to write... (the code can probably be improved, i wrote quickly)

Function ContainsNumbers(text As String) As Boolean
    Dim i As Integer
    Dim numCount As Integer
    For i = 1 To Len(text)
        If IsNumeric(Mid(text, i, 1)) Then [COLOR=#008000]'check each character, count or reset[/COLOR]
            numCount = numCount + 1
        Else
            numCount = 0
        End If
        
        If numCount = 7 Then
            ContainsNumbers = True
            Exit Function
        End If
    Next i
    
    ContainsNumbers = False
End Function

 

[powercell99]

Thanks cerfani! that is really great. i like it alot. how hard would it be to show the character position of the first number instead of True, and FALSE if not found??

 

[jtakw]

Hi,

If there is Only one number string within the sentence,
A formula solution to find the actual Number String, and Position where the Number String starts:

Book1
	A	B	C
1		Number String Found	Position
2	this item is for john in the mail room, 1234567	1234567	41
3	2345678 items are in the mail room	2345678	1
4	john took 3456789 items to the mail room	3456789	11
Sheet73

Cell Formulas
Range	Formula
B2	=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),7)
C2	=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))

Note: Formula will fail if there are More than one 7 digit number strings within the sentence.

 

[cerfani]

well you can just save the position from the i integer when you start the count (numCount = 1) and reset it when you reset numCount

Function SevenNumbersPosition(text As String) As Integer
    Dim i As Integer, numCount As Integer, position As Integer
    For i = 1 To Len(text)
        If IsNumeric(Mid(text, i, 1)) Then [COLOR=#008000]'check each character, count or reset[/COLOR]
            If numCount = 0 Then
                position = i
            End If
            numCount = numCount + 1
        Else
            position = 0
            numCount = 0
        End If
        
        If numCount = 7 Then
            SevenNumbersPosition = position
            Exit Function
        End If
    Next i
    
    SevenNumbersPosition = 0
End Function

 

[Rick Rothstein]

not sure if there is a direct way to do it in excel but in vba the logic is easy to write... (the code can probably be improved, i wrote quickly)

Function ContainsNumbers(text As String) As Boolean
    Dim i As Integer
    Dim numCount As Integer
    For i = 1 To Len(text)
        If IsNumeric(Mid(text, i, 1)) Then [COLOR=#008000]'check each character, count or reset[/COLOR]
            numCount = numCount + 1
        Else
            numCount = 0
        End If
        
        If numCount = 7 Then
            ContainsNumbers = True
            Exit Function
        End If
    Next i
    
    ContainsNumbers = False
End Function

Here is another, more compact way to write your ContainsNumbers function...

[table="width: 500"]
[tr]
	[td]Function ContainsNumbers(S As String) As Boolean
  ContainsNumbers = " " & S & " " Like "*[!0-9]#######[!0-9]*"
End Function[/td]
[/tr]
[/table]

 

[Rick Rothstein]

well you can just save the position from the i integer when you start the count (numCount = 1) and reset it when you reset numCount

Function SevenNumbersPosition(text As String) As Integer
    Dim i As Integer, numCount As Integer, position As Integer
    For i = 1 To Len(text)
        If IsNumeric(Mid(text, i, 1)) Then [COLOR=#008000]'check each character, count or reset[/COLOR]
            If numCount = 0 Then
                position = i
            End If
            numCount = numCount + 1
        Else
            position = 0
            numCount = 0
        End If
        
        If numCount = 7 Then
            SevenNumbersPosition = position
            Exit Function
        End If
    Next i
    
    SevenNumbersPosition = 0
End Function

And here is another, more compact way to write your SevenNumbersPosition function...

Function SevenNumbersPosition(ByVal S As String) As Long
  Dim X As Long
  S = " " & S & " "
  For X = 1 To Len(S) - 7
    If Mid(S, X, 9) Like "[!0-9]#######[!0-9]" Then
      SevenNumbersPosition = X
      Exit Function
    End If
  Next
End Function