0

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?

Qiu
  • 5,651
  • 10
  • 49
  • 56
Emma
  • 1
  • 1
    Using VBA and RegExp might be a better choice http://stackoverflow.com/a/19490129/212869 Using a Pattern of `(/d{1},/d{3})` should match 0,000->9,999 (although its been a while so might be wrong) – NickSlash Jul 13 '15 at 23:36
  • You could revert the text-based number back to a true number in an array formula but that is like digging a ditch just to fill it back up again. Why not use a comma as a thousands separator with true numbers? –  Jul 13 '15 at 23:50

3 Answers3

1

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))
  • Thanks for the suggestion, but I'm implementing a new style guide in blog posts, so unfortunately numbers 1000-9999 need to be written without commas, where as 10,000+ are written with commas, which is why I don't think the wildcard works. – Emma Jul 14 '15 at 15:27
0

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.

NickSlash
  • 4,758
  • 3
  • 21
  • 38
0

Try this: 

=COUNTIF(G:G,"?,???")
rwilson
  • 2,105
  • 11
  • 18