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.