Extract Street Number 3

[Rafool]

Hi,

I'm trying to clean up a large database in Access. I have one field for address, which needs to be broken up into Street Number, Street Name, and Street Label (St., Road, etc.)

The problem is that the data is very dirty. So some addresses will be standard "456 XYZ Road," while others won't have a number and will just say "XYZ Industrial Park," meaning I can't just use Instr to search for the
first space because sometimes the street number needs to be left blank. Rather, I need to use a command to cycle the first few characters and tell if they're numeric or alphabetical, copying them only if they're numeric.
Anyone have any ideas how to do this (i.e. how to tell if they're numeric or not) or any link that has a little code snippet to do this?

Thanks in advance.

 

[GHolden]

Hi,

You can use IsNumeric(variable) which returns true if the variable could be evaluated as a number. More detail is in the help.



There are two ways to write error-free programs; only the third one works.

 

[jksmi]

hi,

you can use the isnumeric function, i.e. isNumeric("123 ") = true while isNumeric("123s") = false. Something like

if isnumeric(left(instr(" ", myString), myString)) then

should work...

HTH, Jamie
FAQ219-2884

 

[Rafool]

Upon closer examination of my data, it appears that there's good news and bad news. The bad news is that often the street number comes after the street name, e.g. XYZ Road 3, so I can't just use Val to pick out the first numerical values from the string, because it will run into the alphabetical characters first and return a -1.
On the other hand, there are no streets with numbers in them. So I just need to take all of the numbers in the string (though some may have characters in the middle, e.g. 13/7, and I want that slash to remain with the numbers) and store them separately. Any idea how I can do that? How can I pull out just the part of the string from the first number to the last number?
Thanks again in advance.

 

[GHolden]

Hi,

The following is an example of code that will extract the numbers from a string...

Dim i As Integer
Dim strAddress As String
Dim strNumber As String

strAddress = " street 112 the"

For i = 1 To Len(strAddress)
  If IsNumeric(Mid(strAddress, i, 1)) Then
    strNumber = strNumber + Mid(strAddress, i, 1)
  End If
Next i

MsgBox strNumber

NB This does not look for consecutive numbers, so...

"1 and 2 and 5 and 9" would return 1259

But should help as a starting point.



There are two ways to write error-free programs; only the third one works.

 

[jksmi]

Hi, try...

Function TestGetNumbers()
    Debug.Print GetNumbers("16 myStreet, whereEver")
    Debug.Print GetNumbers("16 myStreet, whereEver")
    Debug.Print GetNumbers("myStreet 16/7, someWhere")
    Debug.Print GetNumbers("myStreet, elseWhere ")
End Function

Function GetNumbers(ByVal strAddressLine As String) As String
    Dim nLastPos As Long: nLastPos = 1
    Dim nPos As Long: nPos = 1
    Dim strRetVal As String
    
    While nPos > 0
        nPos = InStr(nLastPos, strAddressLine, " ")
        nLen = nPos - nLastPos
        strRetVal = Mid(strAddressLine, nLastPos, 1)
        If IsNumeric(strRetVal) Then
            strRetVal = Mid(strAddressLine, nLastPos, nLen)
            GetNumbers = strRetVal
        End If
        nLastPos = nPos + 1
    Wend
    
    If Len(GetNumbers) = 0 Then
        GetNumbers = "N/A"
    ElseIf Right(GetNumbers, 1) = "," Then
        GetNumbers = Left(GetNumbers, Len(GetNumbers) - 1)
    End If
        
End Function

is this the type of thing your looking for?

HTH, Jamie
FAQ219-2884

 

[Rafool]

You guys are both incredible. Thanks a ton.

 

[MichaelRed]

hmmmmmmmmmmmmmmm ... been there (parsing names and addresses) ... done that ... never completly satiafactory as the variation in data entry is (or at least was) more than any fixed logic could handle. Of coourse, the wider the scope of the data, the more variations in entries there will be, so if the db is either restricted or segmented into smaller subsets the chances of being able to use pattern matching are somewhat enhanced. Some areas (Cities, towns, counties, small countries) have specific formats for the addresses, so these make a good starting point for the segmentation.





MichaelRed
mlred@verizon.net

 

[GummowN]

I'm with MichaelRed on this one.

Data cleaning on a one off basis can often be better achieved though a part manual process.

How about splitting the address string using spaces. Then looking at the first and last columns - the first columns will be numbers when the first letter is not in (A,B,C etc). The last field should contain quite a few counts of Road, Street, Avenue etc

Hopefully it is not a process that needs automation so why not work through the data logically - tidying chunks at a time. Look for groups of data that are the same and work on the chunks individually instead of trying to do the whole lot in one go.

Dirty data is a nightmare to clean.

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.