I have a big address file with street + housenumber in one column. I want to check how many addresses there are without housenumber.
So actually I'm just trying to check if the cells contain a number anywhere
I tried a number of things,
=COUNTIF(A1:A500;{0,1,2,3,4,5,6,7,8,9})
=COUNTIF(A1:A500;"*>0*")
but none seemed to work
Streetname 5 should return TRUE
5 Streetname should return TRUE
Streetname five should return FALSE
Streetname should return FALSE
Try this
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0
You need to check each cell individually. Using the curly brackets generates an array so if any digit is found, FIND returns a number and COUNT goes up by 1.
If no digits are found, COUNT returns 0 and the formula shows FALSE.
5th Avenue?
To my mind the cleanest way to do this is to use regular expressions. Add a VBA module to the workbook with the following function:
Public Function rxTest(inputString As String, pattern As String) As Boolean
Dim r As New RegExp
r.pattern = pattern
rxTest = r.Test(inputString)
End Function
(you will need to add a reference to Microsoft VBScript Regular Expressions). Then the following formula will do what you want for each cell and you can count the number of TRUE cells:
= rxTest(A1,"\d")
You were very close with your COUNTIF function. But since your function contains an array constant, it will return an array of values.
To check how many addresses there are without housenumber:
=COUNTA(A1:A100)-SUM(N(COUNTIF(A1:A100,"*" &{0,1,2,3,4,5,6,7,8,9}&"*")>0))
To return TRUE/FALSE depending on the contents of an individual cell, you can use this formula:
=OR(COUNTIF(A1,"*" &{0,1,2,3,4,5,6,7,8,9}&"*")>0)
Note as pointed out in a comment to another question, this will only detect if there is a digit in the string. It will not differentiate strings where the street name includes a number (eg 5th Ave) from strings that include address numbers. It also won't detect an address where the address number is spelled out (eg One Drury Avenue)
