Count varied number string in a cell

R

RogerSpoon169

New Member
Joined
Apr 6, 2018
Messages
8
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 AColumn 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>
 
Sort by date Sort by votes
RogerSpoon169 said:
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>
Click to expand...
Whether you have a winner or not depends on your answer to the second question I asked in Message #20 .
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Rick,

I did not see all the replies. My mistake.

The date should be ignored for the count I require.

I ran the formula on a few cells and it gave me the answers I was looking for, as follows:

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
Row 2
Inspection

6.17.0 re-coil fibre shielded cable to allow proper pending radius at connector
6.99 Add additional hanger to secure loose cables (3 new cables) sector 3 & sector 1
�
2
Row 3
6.3.2 re-connect grounding cable to ground loop, secure main ground loop cable to penthouse wall.
6.5.1.1 gaps between connectors are exceeding 12", additional hanger is required
6.14.0.3 at all sectors, fibre connector hanger is loose, fibre cable hanger is not using the proper size groumet.
�
2
Row 4
Jan 18th, 2016 - inspection was completed for the project work and the following deficiencies were found;
6.1.5 Need hangers closer to the bend of the cables. Add hanger near the RRU's.
6.12.0.2 Snap-stack hangers not to exceed (2)
6.15.0.2 Bolts anchoring RRU's appear to be undersized. Non standard RRU mount being used.
6.62 Nut/bolt required at each corner of cable tray section (as per note 2.6 of WO). Some screws are also missing in the flashing.
6.99 Tilt bracket and other materials left on site, need to be cleaned up.
8.0 Confirm new antennas do not need to be painted. Sector 3 antenna is above the roof line. Antenna locations do not match drawings. Confirm sector 1 antenna was installed on new mount. Need to add more patio stones to RRU ballast mount.
6

<tbody>
</tbody>

Do you feel there is more tweaking required before I can roll this out?

Thanks.
 
Upvote 0
RogerSpoon169 said:
Rick,

I did not see all the replies. My mistake.

The date should be ignored for the count I require.
Click to expand...
I know you don't what to count the date... you missed the point of the question. IF it is at all possible for the lead in text in a cell to start with a date that looks like this...

13th January 2016

where the first character is a digit, then the formula you think works will not work correctly (try it and you will see the formula counts that date as a code number). Since we know nothing about your data, we have to rely on you telling us what is possible.
 
Upvote 0
Rick,

Without actually going through all the data, I would think that would be possible. The data in the cells is entered by various people and everyone has their own preferences for entering the date.

Thanks.
 
Upvote 0
RogerSpoon169 said:
Rick,

Without actually going through all the data, I would think that would be possible. The data in the cells is entered by various people and everyone has their own preferences for entering the date.
Click to expand...
In that case, you cannot rely in the formula (nor probably any code) posted so far. Can you answer the question about there always being space after the code... will the code you want to count always be followed by a space (or does that fall into your "up to the user" qualification)?
 
Upvote 0
The code will always be followed by a space.

I can probably live with the date problem since I can scrub the data manually to remove it as it is not required for my purposes.

Of course, if a solution could be found so I do not have to scrub the data, that would be great.

Thanks.
 
Upvote 0
RogerSpoon169 said:
The code will always be followed by a space.

I can probably live with the date problem since I can scrub the data manually to remove it as it is not required for my purposes.

Of course, if a solution could be found so I do not have to scrub the data, that would be great.
Click to expand...
See if this UDF works for you...
Code: 
[table="width: 500"]
[tr]
	[td]Function CodeCount(Cell As Range) As Long
  Dim X As Long, Lines() As String, Words() As String
  Lines = Split(Cell.Value, vbLf)
  For X = 0 To UBound(Lines)
    Words = Split(Lines(X))
    If Not Words(0) Like "*[!0-9.]*" And Not Words(0) Like "*..*" And Words(0) Like "*.*" Then CodeCount = CodeCount + 1
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CodeCount just like it was a built-in Excel function. For example,

=CodeCount(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:
Upvote 0
RogerSpoon169 said:
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))}
Click to expand...
RogerSpoon169 said:
... I would think that would be possible. The data in the cells is entered by various people and everyone has their own preferences for entering the date.
Click to expand...
In that case I already showed you way back in post #13 (row 3 of my sample worksheet in that post) that the "winner" formula can return an incorrect result. ;)

Given that you have now said that the code will always be followed by a space, I believe that my UDF in post #13 will already return the correct results. Did you try it?

Rick has already given instructions on how to install & use a UDF but for the record, here are my standard instructions too.
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

.. and here is my function used on the sample data you posted in post #22 + one extra row with the date entered differently.
My functions does give a different answer to what you posted in row 3 but I can't see why you said that answer should be 2 as it appears to me to be 3 and the "winner" answer also returns 3 for that row (but again note that the "winner" answer returns 3 for row 5 in this post when the answer should be 2).

Excel Workbook
AB
1Jan 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
2Inspection 6.17.0 re-coil fibre shielded cable to allow proper pending radius at connector6.99 Add additional hanger to secure loose cables (3 new cables) sector 3 & sector 12
36.3.2 re-connect grounding cable to ground loop, secure main ground loop cable to penthouse wall.6.5.1.1 gaps between connectors are exceeding 12", additional hanger is required6.14.0.3 at all sectors, fibre connector hanger is loose, fibre cable hanger is not using the proper size groumet.3
4Jan 18th, 2016 - inspection was completed for the project work and the following deficiencies were found;6.1.5 Need hangers closer to the bend of the cables. Add hanger near the RRU's.6.12.0.2 Snap-stack hangers not to exceed (2)6.15.0.2 Bolts anchoring RRU's appear to be undersized. Non standard RRU mount being used.6.62 Nut/bolt required at each corner of cable tray section (as per note 2.6 of WO). Some screws are also missing in the flashing.6.99 Tilt bracket and other materials left on site, need to be cleaned up.8.0 Confirm new antennas do not need to be painted. Sector 3 antenna is above the roof line. Antenna locations do not match drawings. Confirm sector 1 antenna was installed on new mount. Need to add more patio stones to RRU ballast mount.6
5Something8.36.32.6 xyz13 Jan 2016 abc63.32 abc2
Count (2)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,032
Messages
6,122,772
Members
449,095
Latest member
m_smith_solihull

Share this page

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
Back
Top