Address and Location analysis often requires the identification of the value in a cell starting with a number (ie house number) or not a number (in the case of a Flat or named house/building for instance).
Using the IsNumeric or Not IsNumeric functions of VBA can help you achieve the analysis of a huge set of data very quickly. Benefits over using the sort function are that you can either remove the row of data or colour the row an identifying colour. Datanology’s street level and postcode database was built using this script, enabling us to identify which addresses were house numbers and which were property names from a large set of part-structured data retrieved from Crown House Sale data.
The following code uses the lastrow script as well and as we are likely dealing with large amounts of data, the Application.Statusbar function is also included to give you an indication of where the data analysis is up to. It checks the first character, and if it is a number it moves on. If it’s not a number AND the next cell across is not blank, it moves the data from column B to column A and prefixes it with ‘1x’ so it passes the test next time.. This is how the data was structured, with Street names being populated in Column B where the property had a name rather than a number.
Feel free to contact us at anytime for assistance on this example code or to enquire about the Excel based postcode database (street level) or the raw postcode and street name data.
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For n = 1 To lastrow
Application.StatusBar = n
If Not IsNumeric(Left(ActiveSheet.Range("A" & n), 1)) And ActiveSheet.Range("B" & n) <> "" Then
ActiveSheet.Range("A" & n) = "1x " & ActiveSheet.Range("B" & n)