+1 vote

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.

Dec 24, 2022 in Others 729 views

## 1 answer to this question.

–1 vote

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}","@"),"@",""))`
• 63,700 points

## Best way to get average values within a specific set of time(say, date or year) using MySQL or Rails

I don't think post-process is what you ...READ MORE

## Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

## Excel: Search for a list of strings within a particular string using array formulas?

If a match is detected, this will ...READ MORE

## How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

## Currency conversion:number to words excel

Try looking for javascript solutions to use ...READ MORE

## Filter/Extract a text from excel sheet having similar values

ISNUMBER(SEARCH("Maria,",SUBSTITUTE(A1:A4,"]",",")))  shows TRUE if Maria is found and false if it would ...READ MORE