• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Separating address number and street name from address1 field


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

1 Solution
philip m o'brienCommented:
Have you tried the ideas here
Arno KosterCommented:
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

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

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now