J
John
I have a need of splitting up an address from a field to seperate the numbers
from the street name.
I found this Regex example posted by Ron Rosenfeld that does some of the job
######################
To use it, enter a formula of the type:
=parseaddr(cell_ref,Index)
where cell_ref contains your original string and Index is a number from 1 to 3
indicating which section of the address you wish to pull out (see the comment
in the UDF).
To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert Module and paste the
code below into the window that opens:
=========================================
Option Explicit
Function ParseAddr(str As String, Index As Long) As String
'Index: 1 = part before street number
' 2 = street number with optional letter
' 3 = part after street number
Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "^((\D+)(\s))?((\d+[A-Z]?)(\s))(.*$)"
If re.test(str) = True Then
Set mc = re.Execute(str)
Select Case Index
Case Is = 1
ParseAddr = mc(0).submatches(1)
Case Is = 2
ParseAddr = mc(0).submatches(4)
Case Is = 3
ParseAddr = mc(0).submatches(6)
Case Else
ParseAddr = ""
End Select
End If
End Function
==============================================
###############
The above works great for addresses formated like
23 Some Street
23a Some Street
But I have things like:
2/3 Some Street
2/3-4 Some Street
Unit 2, 3 Some Street
Unit 2/3 Some Street
I did some reading on Regex but I'm not sure how I can expand on Ron's
original post to also take into account the other formats.
from the street name.
I found this Regex example posted by Ron Rosenfeld that does some of the job
######################
To use it, enter a formula of the type:
=parseaddr(cell_ref,Index)
where cell_ref contains your original string and Index is a number from 1 to 3
indicating which section of the address you wish to pull out (see the comment
in the UDF).
To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert Module and paste the
code below into the window that opens:
=========================================
Option Explicit
Function ParseAddr(str As String, Index As Long) As String
'Index: 1 = part before street number
' 2 = street number with optional letter
' 3 = part after street number
Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "^((\D+)(\s))?((\d+[A-Z]?)(\s))(.*$)"
If re.test(str) = True Then
Set mc = re.Execute(str)
Select Case Index
Case Is = 1
ParseAddr = mc(0).submatches(1)
Case Is = 2
ParseAddr = mc(0).submatches(4)
Case Is = 3
ParseAddr = mc(0).submatches(6)
Case Else
ParseAddr = ""
End Select
End If
End Function
==============================================
###############
The above works great for addresses formated like
23 Some Street
23a Some Street
But I have things like:
2/3 Some Street
2/3-4 Some Street
Unit 2, 3 Some Street
Unit 2/3 Some Street
I did some reading on Regex but I'm not sure how I can expand on Ron's
original post to also take into account the other formats.