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

Who is Participating?
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

philip m o'brienCommented:
Have you tried the ideas here
Another option is to put a ' character in front of any value.

Range("A4") = "'1-3"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.