Excel or Google formula to count occurrences of an 8-digit number within a text string

Question

I want to count the work order numbers that are input in a data collection that has a free text comments box. Since WO numbers are ALWAYS 8 digits long, I thought I had it figured out when I wrote this: In a comment like this: =ROUNDDOWN(SUMPRODUCT(LEN(M7)-LEN(SUBSTITUTE(M7, 0,1,2,3,4,5,6,7,8,9,"")))/8,0) appropriately identifies the WO numbers.

NWMS - 67431710 - Mattress eta pending from contractor
NWMS - 67431797 - Double base eta pending from contractor

and returns a count of 2 (that's a single cell with multi-line comments).

Additionally, given the ROUNDDOWN formula, say the following: odour CMS - 67630485 Only 1 WO is returned by eta 05.02 when it comes through waste outlets or blockages (which is correct)

The latest issue I'm having, though, is that when an ETA date is entered that likewise has 8 digits, like 07/02/2020, this mistakenly adds 1 to the WO number count.

I suppose what I'm looking for is a method of counting blocks of eight consecutive digits within a longer text string. Any more digits, no matter how they are entered, will be excluded.

I work on Google Sheets, but Excel is easier for me to understand, so having a formula for either will be helpful since I can convert it in either format.

Answer 1

To match an eight-digit number, you may try utilizing regular expressions in Google Sheets.
For instance, the following will append @ to every set of eight-digit numbers.

=REGEXREPLACE(M7,"\d{8}","@")

Then, you might count the instances of eight-digit numbers in the string using the len(M7) - len(substitute(... method.

The final formula would look something like

=len(REGEXREPLACE(M7,"\d{8}","@"))-len(SUBSTITUTE(REGEXREPLACE(M7,"\d{8}","@"),"@",""))