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

+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 by Kithuzzz
• 38,020 points
964 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}","@"),"@",""))
answered Dec 24, 2022 by narikkadan
• 63,680 points

Related Questions In Others

0 votes
1 answer
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,017 views
0 votes
1 answer

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

answered Sep 30, 2022 in Others by narikkadan
• 63,680 points
796 views
0 votes
1 answer
0 votes
1 answer

How to split text values by a delimiter?

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

answered Oct 11, 2022 in Others by narikkadan
• 63,680 points
655 views
0 votes
1 answer

Currency conversion:number to words excel

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

answered Oct 28, 2022 in Others by narikkadan
• 63,680 points
595 views
0 votes
1 answer

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

answered Jan 9, 2023 in Others by narikkadan
• 63,680 points
374 views
0 votes
1 answer

Delimiters in Excel VBA Regex Patterns, filter something but not others

Change your code to this: Function RemoveTags(ByVal Value ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,680 points
655 views
0 votes
1 answer

Excel formula to replace or stubstitute only text that starts with a certain letter

You're requesting a "formula." Normally, you would ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,680 points
478 views
0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,680 points
541 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP