I have to import an address file into an excel sheet (I can do that) but one
of the columns in the file is a text field that contains the important house
number mixed with text:
The Old Vicarage 29 The High Street
T & G Plumbers 30 Long Road
The Corner Shop 2 Village Green
These text strings need splitting into 3 separate cells, data before the
number, the number, and data after the number. Can anyone help please.
Here's a UDF that should handle that:
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
==============================================
--ron
