Add'l

[WGD]

For how it may be useful to you, using Office 2007.
Wayne

From: "Ron Rosenfeld" <[email protected]>
Subject: Re: Extension need beyond "Split a street address apart"
Date: Wednesday, March 26, 2008 8:19 PM

Ron:

837 Third Ave ok, can split this into: B1: 837 C1: Third Ave
PO Box 45 would like this: B1: C1: PO Box
45
APO AE 09456 would like this: B1: C1: APO AE
09456

BTW,
For the 837 Third Ave I used:

A1: (address text....eg 837 Third Avenue)

The number
B1: =LEFT(A1,FIND(" ",A1)-1) = 837

The street
C1: =TRIM(MID(A1,FIND(" ",A1)+1,255)) = Third Ave

Does this help?
Thank You!
Wayne

Wayne,

I find this simplest to do using a UDF that implements Regular Expressions.
The
UDF splits the address into two portions (actually 3, but we are only
interested in #2 and #3).

1. The first portion has to be the First word and must also contain a
number.
So it will include 837; but would also include address numbers where the
address consists of mixed numbers and letters (and punctuation).

2. The second portion is anything else.

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, with your full address in A1, enter a formula into some
cell:

=remidx(A1,"((\S*\d\S*)\s)?(.*)",2) --> address number

and, in another cell:

=remidx(A1,"((\S*\d\S*)\s)?(.*)",3) --> street or APO or PO Box

========================================
Option Explicit
Function ReMidx(Str As String, sPattern As String, Index As Long) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
If re.test(Str) = True Then
Set mc = re.Execute(Str)
ReMidx = mc(0).submatches(Index - 1)
End If
End Function
===========================================

This should do the trick.
--ron