Separate single address field into house number and street address

[Garindan]

Hi all, many thanks for reading.

I have a customer database which i built. I have always used two separate fields for 'House/Flat Number' and 'Street', and there are 6000 correct customers entered in this way.

The company has now bought 35000 leads for marketing, which will be added to the database. However the Excel file received only has one field/column for address which contains both the house numbers and street. So now I need to separate them.

The table and fields are tblCustomers and 'House_FlatNumber', 'Street'.

I tried looking myself and I know I need an update query with a formula in the update field, but i'm unsure of the best way to have the formula as the existing street addresses can be mixed up.

Here are some examples from the excel file, the majority are straightforward i.e. 76 Petersfield Road, but some are more difficult:-

Flat 20 Riviera
Riviera
Edenholme 1a
Flat 1c 59 Grove Road
Eden Court
Flat 1 (many are listed just as a flat number! but i've been told they can find the correct address as they get to them so its not a problem)
7 Red Hill Park Homes Wimborne Road

So as you can see they're a mixture! However on second looking the vast majority are just number/street/road, i.e. 76 Peterfield Road.

So would it be best to take two spaces from the right and enter that into street field? How would that deal with one word entries such as Riviera, etc? This is where I get a little confused and need help

Many thanks in advance for any help given!!!

 

[jal]

Using your mouse, import the excel sheet into an Access table. Add a column called House_FlatNumber and a column called Street. Also add a column (Yes/No or True/False) called "Success".

Then using VBA, use a recordset to loop through the table one row at a time, using the following logic:

Set "Success" to false for all rows.

If the first word on the line is numeric, accept it as the house number, and presume the remaining the words to be the street name. Set "Success" to True for this row.

If the first word is "Flat" and the second word is a number (as in Flat 20), presume the first two words (i.e. Flat 20) to be the house number, and presume the rest of the words to be the street name. Set "Success" to True for this row.

This should take care of the vast majority of records. Then, using your mouse, find all those records where "Success" is still set to false. You may have to handle these manually, using your keyboard and mouse, unless you can see some patterns similar to the ones above, and can figure out how to code some logic to automatically process them.

Do you have enough VBA experience to get started?

 

[Brianwarnock]

I had to do this a life time ago when I was new to Microsoft coding, I did it in Excel so if you are happy to use crap code that has the one redeeming feature that it appears to work you can give this a try.

Place your cursor on the first cell in the address column and run the macro.

Sub parseadd()

ActiveCell.Select
ActiveCell.Offset(0, 1).EntireColumn.Insert
ActiveCell.Offset(0, 2).EntireColumn.Insert
Do Until IsEmpty(ActiveCell)    'loop through rows
    mylen = Len(ActiveCell)
    bpos = mylen - 1
    
    Do Until bpos = 0
    If IsNumeric(Mid(ActiveCell, bpos, 1)) Then GoTo num:
    bpos = bpos - 1
    Loop
  ' No numbers place in onecell
onecell:
  ActiveCell.Offset(0, 2).Select
    ActiveCell.Value = Selection.Offset(0, -2)
  GoTo reloop
    
num:
    splitadd = InStr(bpos, ActiveCell, " ")
    If splitadd = 0 Then GoTo onecell
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Mid(Selection.Offset(0, -1), (splitadd))
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Left(Selection.Offset(0, -2), (splitadd))
    

reloop:
ActiveCell.Offset(1, -2).Select
Loop
 GoTo fini
 

fini:
End Sub

Or you could write some decent code with the variables defined etc.

Oh its best not to have any other work books open as Activecell can get fooled.

Brian

 

[Brianwarnock]

So would it be best to take two spaces from the right and enter that into street field? How would that deal with one word entries such as Riviera, etc? This is where I get a little confused and need help

This approach is as valid as any given that no approach will find all, its just that the "missed" ones will probably be different, which is best for your data only you can say.

This will miss
10 West Bank Street
1 Moore Lane West
1 Broadway

To use this approach use 2 InstrRev in your Sub (look it up in VBA help)

FirstSpace=Instrrev(yourfield," ")
If firstspace=0 Then this means only one string copy to House field


else
SecondSpace=Instrev(yourfield," ",firstspace-1)

If SecondSpace=0 Then non standard copy to House Field

Else this is the place for the split

House=Left(yourfield,secondspace-1)
Street=Mid(yourfield,Secondspace+1)


You can check all records where Street is empty as a first visual pass.


Brian

 

[Brianwarnock]

I was so ashamed of my first code I have taken a few minutes to write a version splitting on the last 2 strings.

Brian

Sub parseadd2()
'Brian Warnock Jan 2011
'This splits a single address field into 2
'The right 2 strings are placed in one cell and the rest into another
' This code assumes the data starts in A1, adjust according to need

Dim firstspace As Long
Dim secondspace As Long
Dim rowa As Long
Dim lngLastRow As Long

With Sheets("sheet1").UsedRange
lngLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With

Columns("B").EntireColumn.Insert
Columns("C").EntireColumn.Insert

rowa = 1
Do Until rowa > lngLastRow
    firstspace = InStrRev(Cells(rowa, 1), " ")
    If firstspace = 0 Then
        Cells(rowa, 2) = Cells(rowa, 1)
        GoTo getnext
    Else
        secondspace = InStrRev(Cells(rowa, 1), " ", firstspace - 1)
        If secondspace = 0 Then
            Cells(rowa, 2) = Cells(rowa, 1)
            GoTo getnext
        Else
            Cells(rowa, 2) = Left(Cells(rowa, 1), secondspace - 1)
            Cells(rowa, 3) = Mid(Cells(rowa, 1), secondspace + 1)
        End If
    End If

getnext:
rowa = rowa + 1
Loop

End Sub

 

[DCrake]

If I had 6000 that were in my original file and then got another 35000 that needed amending then why don't you change your 6000 to match the 35000? Seems a better and quicker solution.

 

[vbaInet]

If I had 6000 that were in my original file and then got another 35000 that needed amending then why don't you change your 6000 to match the 35000? Seems a better and quicker solution.

If and only if the quicker solution follows normalization rules. Although, having two fields for address still isn't properly normalized but it's close. Also, it would require having to change field names, queries, forms etc to suit the new structure. If I had millions of records then maybe I would consider this option. But 35k is doable using code.

 

[DCrake]

What about using the split function with a space delimiter then loop backwards though the array until you find a numeric array item. As soon as you do the know that everything from that position + 1 to the ubound items are all strings and you concat them back together and post them into the address field. Everything upto that will be in adr1.

Aircode

Flat 21 Arcadia Road Anytown

Becomes

0 Flat
1 21
2 Arcadia
3 Road
4 Anytown

2, 3 & 4 are for adr2
0 & 1 are for adr1

 

[Garindan]

Many thanks for all your replies! I'm very grateful!

I tried using Brianwarnocks code to split the data in excel (version 2), but i'm getting an error "runtime error 9. subscript out of range" Any ideas what i'm doing wrong?

Other than that i'm a bit of a novice with vba and i cant work out how to add the strings to do it in access. Sorry!

 

[vbaInet]

I'm sure Brian will come back with a fix to that problem but in the meantime can you explain these lines:

Flat 20 Riviera
Riviera
Edenholme 1a

Are the separate records or is it supposed to be this:

Flat 20 Riviera Riviera
Edenholme 1a

Or this:

Flat 20 Riviera Riviera Edenholme 1a

??

 

[Brianwarnock]

I posted two macros, both of which run happily on my little test file consisting of your examples and a few others in Excel 2002.

Perhaps you could attach your file, if not say which sub you used and give information about the row it failed on.

Brian

 

[Garindan]

vbaInet - they are separate records, thanks
Brian - It didn't say which row it failed on, i'm probably doing something wrong :/
I've attached the file with one record now. Many thanks.

 

[vbaInet]

Ok, with respect to these two records, which field would they be saved into?

Edenholme 1a
Eden Court

??

 

[Garindan]

vbaInet - They are two of the problem records. Eden Court and Edenholme 1a are probably house names and numbers so would be in House/Flat_Number only.

There are only a few like this really and will be fine to manually correct them. The biggest number of problems/mistakes are all the flat numbers, flat 1, flat 7 etc with missing street names, but they'll just have to go into House/Flat_Number and be found and updated with their post codes. Thanks.

 

[Brianwarnock]

The first Sub would work as designed but not as you wish as you have created 2 empty columns for the split, both subs do that so mods need to be made.
The second sub also needs to be modified to take into account where your data is as I said it was written for data starting in A1 whereas yours starts in D2.

As you are a beginner I have modified it for you, but please take time to understand it, you can select the functions such as Cells and hit F1 to go to help.

Brian

Sub parseadd2()
'Brian Warnock Jan 2011
'This splits a single address field into 2
'The right 2 strings are placed in one cell and the rest into another
' This code assumes the data starts in A1, adjust according to need

'Amended for data starting in D2 and split fields going into cols E and F

Dim firstspace As Long
Dim secondspace As Long
Dim rowa As Long
Dim lngLastRow As Long

With Sheets("query1").UsedRange
lngLastRow = .Cells(1, 4).Row + .Rows.Count - 1
End With


rowa = 2
Do Until rowa > lngLastRow
    firstspace = InStrRev(Cells(rowa, 4), " ")
    If firstspace = 0 Then
        Cells(rowa, 5) = Cells(rowa, 4)
        GoTo getnext
    Else
        secondspace = InStrRev(Cells(rowa, 4), " ", firstspace - 1)
        If secondspace = 0 Then
            Cells(rowa, 5) = Cells(rowa, 4)
            GoTo getnext
        Else
            Cells(rowa, 5) = Left(Cells(rowa, 4), secondspace - 1)
            Cells(rowa, 6) = Mid(Cells(rowa, 4), secondspace + 1)
        End If
    End If

getnext:
rowa = rowa + 1
Loop

End Sub

 

[Garindan]

Brian, apologies, the two blank columns were already there in the file. I should have removed them first. Anyway the code works perfectly. Many many thanks for your help and time!!!

 

[Brianwarnock]

No problem, glad its working for you.

Brian

 

[delange]

Hi Brian... I know you post is very old... I hope you or someone else can assist. I used your code and it works perfect however. I need to have the address parsed into 3 sections ...ex

client home 9678 gremlin circle, Hampton VA 23111

I need it to be split into 5 columns splitting like so name/street address/city/state/zip .



client home /9678 gremlin circle/ Hampton/ VA /23111





Can you help with a code that can do that.. Thanks

 

[delange]

How can I remove the alpha text to the (right) preceding the first instance of a number.... Ex

Center Base 9600 Drive Ln
Center West 6600 Davis st
Quest Center 8500 Lyn Ave

I want only the street address returned for each cell

9600 Drive Ln
6600 Davis st
8500 Lyn Ave

thanks

 

[Brianwarnock]

It is long enough ago that I no longer have Access but it goes something like this for a field called flda

Mid(flda,instr(instr(flda," ")+1,flda," ")+1)

The inner instr finds the position of the first space the outer finds the position of the second space and thus we can get the start position of the data we want, a mid without a length extracts all characters after the start position.

Brian