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!

Column A

Column B

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.

8

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(LEFT(A2:A12,1)+0),IF(ISNUMBER(LEFT(A2:A12,2)+0),1)))

 

[RogerSpoon169]

I neglected to mention that all that text under Column A is contained within a single cell. Sorry for leaving that important info out.

Should actually appear as:

	Column A	Column B
Row 1	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.	8

<tbody>
</tbody>

Thanks!

 

[Aladin Akyurek]

You also neglectet post #2 !...

 

[jtakw]

You also neglectet post #2 !...

Aladin, OP is saying that the Entire Block of Data is All in One Cell, e.g. A1, only, Not A1:A12.

 

[Rick Rothstein]

I neglected to mention that all that text under Column A is contained within a single cell. Sorry for leaving that important info out.

Should actually appear as:

	Column A	Column B
Row 1	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.	8

<tbody>
</tbody>

Are the numbers you want to count bolded in the cell as shown or did you bold them just for you posting?

If you bolded them just for your posting, then what makes the number I highlighted different from the bolded number above it that you are not counting it?

Do all the numbers you want to could always appear at the beginning of a new line as shown in your posting?

 

[RogerSpoon169]

Rick,

I did make a mistake and forget to bold "8.0". It should be part of the count I want to achieve, as per the count I listed in Column B.

I put them in bold for this posting. All the text in the cell is unformatted.

Yes, all the numbers would appear at the start of any line as per my posting example.

Thanks.

 

[Scott Huish]

Perhaps this UDF:

Function CountNumbers(r As String) As Long
Dim t, u, ct As Long
t = Split(r, Chr(10))
For Each u In t
    If IsNumeric(Left(u, 1)) Then ct = ct + 1
Next
CountNumbers = ct
End Function

Excel 2010
	A	B
1	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.	8
Sheet1

Cell Formulas
Range	Formula
B1	=countnumbers(A1)

 

[rlv01]

Counting all the numbers in a cell is straightforward, but you've said that the only numbers you want to count are the ones that appear at the beginning of a line. If all your example text is in cell A1 as a big blob of text, how are you defining "line" for purposes of this example?

 

[Rick Rothstein]

Perhaps this UDF:

Function CountNumbers(r As String) As Long
Dim t, u, ct As Long
t = Split(r, Chr(10))
For Each u In t
    If IsNumeric(Left(u, 1)) Then ct = ct + 1
Next
CountNumbers = ct
End Function

Just to compact your function up a little bit...

Function CountNumbers(S As String) As Long
  Dim V As Variant
  For Each V In Split(S, vbLf)
    CountNumbers = CountNumbers - IsNumeric(Left(V, 1))
  Next
End Function