Separating address number and street name from address1 field

Posted on 2011-05-04
Last Modified: 2012-05-11

I've been trying to find a program which splits a house number from a street name, I have ten of thousands to go through and the addresses are all in different forms. I have come across the below piece of code which seems to work marginally well. The only problem is that when it comes across an address like 1-3 COMMERCIAL ROAD, the excel cell automatically changes the 1-3 to 3-jan and I can't easily convert it back to 1-3. This even happens when I change the target column to a text format. Is there any way to get around this? Or alternatively is there any code out there that does the job better? I don't care if I do this in excel or access 2003.

Sub parseadd()

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
  ' No numbers place in onecell
  ActiveCell.Offset(0, 2).Select
    ActiveCell.Value = Selection.Offset(0, -2)
  GoTo reloop
    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))

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

End Sub

Question by:GreenV27
    LVL 7

    Expert Comment

    by:philip m o'brien
    Have you tried the ideas here
    LVL 19

    Accepted Solution

    at my workplace, both solutions work and do not convert the 1-3 to a date :
    Range("A2").Formula = "=""1-3"""
    Range("A4").NumberFormat = "@"
    Range("A4") = "1-3"

    Open in new window

    LVL 10

    Expert Comment

    Another option is to put a ' character in front of any value.

    Range("A4") = "'1-3"

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now