Formula to identify cells that have 7 numbers with a string

powercell99

Board Regular
Joined
May 14, 2014
Messages
75
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
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)

G4RyofX.png


Code:
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
 
Last edited:

powercell99

Board Regular
Joined
May 14, 2014
Messages
75
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,416
Office Version
  1. 2016
Platform
  1. Windows
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Number String Found</td><td style=";">Position</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">this item is for john in the mail room, 1234567</td><td style="text-align: right;;">1234567</td><td style="text-align: right;;">41</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">2345678 items are in the mail room</td><td style="text-align: right;;">2345678</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">john took 3456789 items to the mail room</td><td style="text-align: right;;">3456789</td><td style="text-align: right;;">11</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet73</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=MID(<font color="Blue">A2,MIN(<font color="Red">FIND(<font color="Green">{0,1,2,3,4,5,6,7,8,9},A2&"0123456789"</font>)</font>),7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=MIN(<font color="Blue">FIND(<font color="Red">{0,1,2,3,4,5,6,7,8,9},A2&"0123456789"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136

ADVERTISEMENT

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

d9b2ikJ.png


Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,692
Office Version
  1. 2010
Platform
  1. Windows
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)

G4RyofX.png


Code:
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...
Code:
[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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,692
Office Version
  1. 2010
Platform
  1. Windows
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

d9b2ikJ.png


Code:
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...
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,939
Messages
5,598,967
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top