CODEWITHSUNDEEP
excelexcel-formula
Emma
Emma

Reputation: 1

How do I search for numbers within a range that are written in a specific format?

I am trying to write an Excel formula that measures the number of times a number between 1000 and 9999 is written in text using the format 0,000. (This is being used to read old content from our website and measure how many pages do not align with a new style guide.) Here is what I have so far:

=count(search(text(1000,"0,000"),G17))

This formula works if the text in the content is 1,000, but, obviously, not if the text is 1,001.

I don't know how to enter the range in. I assume it should go where the 1000 is, but nothing I try works.

Does anyone know how to do this?

Upvotes: 0

Views: 318

Answers (3)

rwilson
rwilson

Reputation: 2145

Try this: 

=COUNTIF(G:G,"?,???")

Upvotes: 0

user4039065
user4039065

Reputation:

If your text-based number values in column G are between 0 and 999,999 then this should return a count of all text-based numbers that would have a numerical value between 1000 and 9999 if they were actually numbers.

=SUMPRODUCT(COUNTIF(G:G, {"1,*","2,*","3,*","4,*","5,*","6,*","7,*","8,*","9,*"}))

Another approach is that anything between 1,000 and 9,999 is going to have a length of 5.

=SUMPRODUCT(--(LEN(G:G)=5))

Upvotes: 1

NickSlash
NickSlash

Reputation: 5075

If you add the following code to a new "Module" in the VBA Editor you will have access to it as a worksheet function.

I've not tested it all that much but it worked for my example.

Public Function RESearch(SourceText) As Integer
Dim REO As Object: Set REO = CreateObject("VBScript.RegExp")
REO.Pattern = "(\d{1},\d{3})"
REO.Global = True
REO.IgnoreCase = False
REO.MultiLine = True
Dim Matches As Variant
Set Matches = REO.Execute(SourceText)
RESearch = Matches.Count
Set REO = Nothing
End Function

This will add a function "RESearch" to the workbook, and should return the count of all numbers that match the pattern.

Upvotes: 0

Related Questions