Splitting up address
 

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.

Splitting up address
 

select the range with addresses and try the following macro:

Sub riplace()
For Each cell In Selection
counter = 0
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = Chr(32) Then
counter = counter + 1
If counter = 2 Then
cell.Offset(0, 1) = Chr(39) & Left(cell, i - 1)
cell.Offset(0, 2) = Right(cell, Len(cell) - i + 1)
End If
End If
Next i
Next cell
End Sub

HIH

Splitting up address
 

use:

cell.Offset(0, 1) = CStr(Left(cell, i - 1))

instead of:
cell.Offset(0, 1) = Chr(39) & Left(cell, i - 1)

sorry

Splitting up address
 

you might also try a function:

Function riplace(cell As Range, opcja As Integer) As String
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = Chr(32) Then
counter = counter + 1
If counter = 2 Then
If opcja = 1 Then
riplace = CStr(Left(cell, i - 1))
ElseIf opcja = 2 Then
riplace = Right(cell, Len(cell) - i)
End If
End If
End If
Next i
End Function

2nd argument of the function determins which part of an address to
retrieve:
1 is for 2/3, 2/3-4, Unit 2, 3, Unit 2/3
2 is for Some Street