Extract House Number (1 Viewer)

[Uncle Gizmo]

I needed to extract the house number from the first line of an address for a HMRC GiftAid Claim form.

HMRC requirements are that the field should contain either the house number or the house name, and no other information. The next field should contain the postcode.

My assumption is that the first line of the address will contain either the house number and street name or house name. If the first character(s) are numbers, then that's the house number. If there is/are NO NUMBER(s) then it's the house name. This is for the HMRC Gift Aid Spreadsheet found HERE:- Gift Aid: schedule spreadsheets to claim back tax on donations

I found this AWF thread:- Extract numbers from text

and have adapted it to my needs and this is the result.
I have only tried it on some dummy data, will be interesting to see how it operates on the real data!!!

Function fGetHouseNumberOrName(ByVal strInString As String) As String
'Found HERE:-
'Adapted from raskew's http://www.access-programmers.co.uk/forums/member.php?u=4714 original code
'Extract numbers from text
'http://www.access-programmers.co.uk/forums/showthread.php?t=219652

Dim intLen  As Integer
Dim intCounter As Integer
Dim strNumber As String
Dim blnFoundNumber As Boolean

blnFoundNumber = False
strInString = Trim(strInString) 'Removes leading & trailing spaces
intLen = Len(strInString)       'Stores original length
intCounter = 1                  'Counter & position marker

    If strInString = "" Or IsNull(strInString) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
    
            Do
                If IsNumeric(Mid(strInString, intCounter, 1)) Then              'Check if that single character is a number
                    blnFoundNumber = True
                    strNumber = strNumber & Mid(strInString, intCounter, 1)     'If it is add to existing ones (if any)
                    intCounter = intCounter + 1                                 'Add to counter so we know to go to next character on the next pass/loop
                Else
                    If intCounter = 1 Then Exit Do                              'The first Character "is Not Numeric" so don't Check any more, so Exit
                    If blnFoundNumber = True Then Exit Do                       'This Character is "is Not Numeric" so your House number is finished, so Exit
                    intCounter = intCounter + 1                                 'It "is Not Numeric", add to counter so we know to skip it
                End If
            Loop Until intLen = (intCounter - 1) 'Go until we processed all characters. The reason we have to do intCounter -1 is that Len starts at 0 & we told intCounter to start at 1

    If IsNumeric(strNumber) Then
        fGetHouseNumberOrName = strNumber
    Else
        fGetHouseNumberOrName = strInString
    End If
    
End Function      'fGetHouseNumberOrName

 

[CJ_London]

you could use the val function instead

if val(strInString)=0 then 'no housenumber
....
....
else
....
....
end if

this has the benefit of ignoring preceding spaces

But what about other scenarios from your assumption? e.g.

Flat 10
Apt 25
No 6

 

[Uncle Gizmo]

Hi Chris, Haven't spoke for a while! My other assumption (I neglected to mention it) is that the first few characters will be the house number, subsequent numbers after the first few numbers should be ignored.

I note prefixes like flats and other things might cause an issue. I could write code for detecting these exceptions but I think it will be easier to edit the exceptions manually.

 

[jdraw]

My suggestion with this sort of thing is
- get some sample data and get some feel for the range of data you may receive
- over the years I've seen a variety of address information, in various random order
- be wary of what this really means (real world)

the field should contain either the house number or the house name, and no other information. The next field should contain the postcode.

- be prepared to revisit/revise your algorithm(s).

Certainly you have an excellent start --devil is always in the details and real data.

Good luck.

 

[Brianwarnock]

One simple situation that your code will not find, I think, is when the number has a suffix. This occurs when a large property is demolished and two smaller properties are built on the site, then you end up with 9a for example. This is not uncommon and I think that your code can be easily tweaked to handle this.

It is too many years since I did this, I've been retired 9, but I'm sure I found it useful to extract a file of all records that failed to provide a result and thus needed manual handling. Sorry I cannot be more helpful.

Brian

 

[Uncle Gizmo]

You are right Brian, I think that a suffix might be worth detecting. I'm hoping to get a look at the real data sometime next week. I will make that decision then.

I think it's about a thousand records per year, which are potentially entered by different people. Because it's a charity it's difficult to; shall we say get people to conform to a particular data entry regime. For that reason I think it might be best to spend more time on creating a good interface so that it is easy to edit the data.

The other problem is I would like to change the table layout so that the user could enter the house name or number in separate columns, this might prove difficult to enforce and the other issue is I would then have to change all of the label printing reports, this is something I wish to avoid for the time being.