CODEWITHSUNDEEP
excel
Nautilus
Nautilus

Reputation: 115

Check if string contains number

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

Upvotes: 1

Views: 1980

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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)

Upvotes: 0

aucuparia
aucuparia

Reputation: 2051

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")

Upvotes: 0

SJR
SJR

Reputation: 23081

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.

enter image description here

Upvotes: 3

Related Questions