Count varied number string in a cell

[RogerSpoon169]

I would like to count the number of times a number string (numbers in bold in Column A) occur in a cell (# in Column B) throughout my file of XXX # of rows.

The number string varies in length but the format is always constant (i.e. #.##.##.#).

Thanks, in advance, to all for your help!
[TABLE="width: 489"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]
Jan 13th, 2016 - inspection was completed for the project work and the following deficiencies were found;
5.80 Old blocks left on site need to be removed. Garbage and bolts laying on site to be removed.
6.5.1.2 No labels for the fiber in the base of the DCI.
6.62 Nut/bolt required at each corner of cable tray section (as per note 2.6 of WO). Some screws are also missing.
6.63 All cable tray openings to be secured with 1/4" galv. steel mesh
6.99 Fiber cable laying in bottom of cabinet is properly terminated.
8.0 "Sector 1 - Existing blocks on site are not solid as indicated on WO. New blocks are not as per WO. Confirm antenna heights are correct.
Sector 2 - Don?t have the proper number of blocks to match WO.
Sector 3 - LTE - Existing blocks on site are not solid as indicated on WO. New blocks are not as per WO. GSM/UMTS - Quantity of new blocks for mounts do not match WO. Antennas have not been raised as per WO. "
8.5.0.1 Rust has started to appear on some of the new steel installed.
8.99 Confirm SC6 audit was completed for the antenna changes.[/TD]
[TD]8[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[István Hirsch]

Or you can try this formula (confirm with Ctrl -Shift - Enter):

=SUM(IF((MID(CHAR(10)&A2,ROW($1:$3000),1)=CHAR(10))*(ISNUMBER(-MID(CHAR(10)&A2,ROW($1:$3000)+1,1)))=1,1,0))

 

[Rick Rothstein]

Or you can try this formula (confirm with Ctrl -Shift - Enter):

=SUM(IF((MID(CHAR(10)&A2,ROW($1:$3000),1)=CHAR(10))*(ISNUMBER(-MID(CHAR(10)&A2,ROW($1:$3000)+1,1)))=1,1,0))

Same underlying logic, but written using SUMPRODUCT (and eliminating the IF function call) so that it can be normally entered.

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=CHAR(10)),--ISNUMBER(-MID(A1,1+ROW(INDIRECT("1:"&LEN(A1))),1)))

 

[Peter_SSs]

I'm not sure what is possible with the OP's data, but if a line can start with "Jan 13th, 2016" (as the example does), I'm thinking it may be quite possible for a line to start with "13th Jan 2016".
If that is so, all the formula and UDF suggestions so far would count that line, yet it does not appear to meet the OP's (somewhat confusing) stated information:

The number string varies in length but the format is always constant (i.e. #.##.##.#)

This is my suggestion for a UDF that addresses that point.

Function CountNums(s As String) As Long
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.MultiLine = True
  End If
  RX.Pattern = "(^\d+\.)+\d+"
  CountNums = RX.Execute(s).Count
End Function

All suggestions (showing different results in row 2)

Excel Workbook
	A	B	C	D	E
1	Data	Peter	Scott/Rick	Istvan	Rick
2	Jan 13th, 2016 - inspection was completed for the project work and the following deficiencies were found;5.80 Old blocks left on site need to be removed. Garbage and bolts laying on site to be removed.6.5.1.2 No labels for the fiber in the base of the DCI.6.62 Nut/bolt required at each corner of cable tray section (as per note 2.6 of WO). Some screws are also missing.6.63 All cable tray openings to be secured with 1/4" galv. steel mesh6.99 Fiber cable laying in bottom of cabinet is properly terminated.8.0 "Sector 1 - Existing blocks on site are not solid as indicated on WO. New blocks are not as per WO. Confirm antenna heights are correct.Sector 2 - Don?t have the proper number of blocks to match WO.Sector 3 - LTE - Existing blocks on site are not solid as indicated on WO. New blocks are not as per WO. GSM/UMTS - Quantity of new blocks for mounts do not match WO. Antennas have not been raised as per WO. "8.5.0.1 Rust has started to appear on some of the new steel installed.8.99 Confirm SC6 audit was completed for the antenna changes.+	8	8	8	8
3	Something8.36.32.6 xyz13 Jan 2016 abc63.32 abc	2	3	3	3
Count

 

[Rick Rothstein]

I'm not sure what is possible with the OP's data, but if a line can start with "Jan 13th, 2016" (as the example does), I'm thinking it may be quite possible for a line to start with "13th Jan 2016".
If that is so, all the formula and UDF suggestions so far would count that line, yet it does not appear to meet the OP's (somewhat confusing) stated information

Maybe this for my CountNumbers UDF then....

[table="width: 500"]
[tr]
	[td]Function CountNumbers(S As String) As Long
  Dim V As Variant
  For Each V In Split(S, vbLf)
    CountNumbers = CountNumbers - (Not Split(V, ".")(0) Like "*[!0-9]*")
  Next
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

Maybe this for my CountNumbers UDF then....

As before, I don't know what data is possible, but your function would still count ..
- a line containing just a number like 13 and nothing else, and
- a line containing 13.Jan.2016 abc
- a line containing .xyz
.. none of which seem to fit the suggested pattern

 

[Rick Rothstein]

Quote Originally Posted by Rick Rothstein View Post
Maybe this for my CountNumbers UDF then....

As before, I don't know what data is possible, but your function would still count ..
- a line containing just a number like 13 and nothing else, and
- a line containing 13.Jan.2016 abc
- a line containing .xyz
.. none of which seem to fit the suggested pattern

Assuming there is a space after the code the OP is looking for, then I think this will work...

[table="width: 500"]
[tr]
	[td]Function CountNumbers(s As String) As Long
  Dim V As Variant, W() As String
  For Each V In Split(s, vbLf)
    W = Split(V & " ")
    CountNumbers = CountNumbers - ((Not Replace(W(0), ".", "") Like "*[!0-9]*") And (W(0) Like "*#.#*"))
  Next
End Function[/td]
[/tr]
[/table]

I know your code does not rely on there being a space after the code, but because of that, it will count things like 1.2.a, 1.2-a, 1.2:3, etc. which I would guess is not what the OP wants either.

 

[Peter_SSs]

I just realised that I have a misplaced opening parenthesis in my post #13 function. The code should be:

Function CountNums(s As String) As Long
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.MultiLine = True
  End If
  RX.Pattern = "^(\d+\.)+\d+"
  CountNums = RX.Execute(s).Count
End Function

If there was a requirement for a space after the code the OP is looking for (though there is no mention of such), then my pattern line line would become

RX.Pattern = "^(\d+\.)+\d+ "

Assuming there is a space after the code the OP is looking for, then I think this will work...

.. though then it would count 1.2. a which again does not appear to fit the pattern. (Though it does seem to correctly exclude 1. a)

It really would be good if the OP could determine which, if any, of the suggestion meets the requirement as we could back-and-forth with possible examples for a long time.

 

[RogerSpoon169]

Thanks for your help but I have a question as to how to enter this into excel so that your function will work.

Sorry but I am not at all familiar with the programming involved.

Thanks!

 

[RogerSpoon169]

We have a winner ....

D2	{=SUM(IF((MID(CHAR(10)&A2,ROW($1:$3004),1)=CHAR(10))*(ISNUMBER(-MID(CHAR(10)&A2,ROW($1:$3004)+1,1)))=1,1,0))}

<tbody>
</tbody>

Thanks everyone for your help! Much appreciated.

 

[Rick Rothstein]

Thanks for your help but I have a question as to how to enter this into excel so that your function will work.

Sorry but I am not at all familiar with the programming involved.

I am not sure you have read all the posted messages, but if you had, you would see we are kind of guessing around because we are unsure of the format of your data....

Is there always a space immediately after the code numbers you want to retrieve?

Is it possible for the beginning of your text to start with a number that is not part of the code numbers you are after? The example of this that Peter wondered about was if the text could start with January 13, 2016, could it possibly start with 13th January 2016?