Extension need beyond "Split a street address apart"

[WGD]

This group provided a pair of very quick and neat solutions to the splitting
of an address, i,.e 123 Adams St. the 123 in one cell and the Adams St in
another. (Currently using the first posted solution).

Some of my addresses now fell into the PO BOX and APO formats. In addition
to using the solutions (post below dtd 3/19 in this group), how might I
put,add to what I now have, PO Box 45 in the same column as the Adams St?
Also APO AE (military base addresses)?

Thank You!
Wayne

 

[Ron Rosenfeld]

This group provided a pair of very quick and neat solutions to the splitting
of an address, i,.e 123 Adams St. the 123 in one cell and the Adams St in
another. (Currently using the first posted solution).

Some of my addresses now fell into the PO BOX and APO formats. In addition
to using the solutions (post below dtd 3/19 in this group), how might I
put,add to what I now have, PO Box 45 in the same column as the Adams St?
Also APO AE (military base addresses)?

Thank You!
Wayne

Please post a bunch of examples of your various types (formats) of data.
--ron

 

[WGD]

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

 

[Ron Rosenfeld]

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

 

[WGD]

Ron: NOT VBA-experienced at all. I entered your =remidx . . . statement
in B1 (with the address to be split in A1). #NAME? popped up in B1.

Clearly I did something basically wrong. But what?

Tks!
Wayne

 

[Ron Rosenfeld]

Ron: NOT VBA-experienced at all. I entered your =remidx . . . statement
in B1 (with the address to be split in A1). #NAME? popped up in B1.

Clearly I did something basically wrong. But what?

Tks!
Wayne

When you entered the code into a regular module (which you should have done
first), did you make sure your CURRENT project was highlighted? If you had the
wrong project highlighted, you might get a #NAME! error.

I repeat those directions and the code below:

-----------------------------------------------

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.

========================================
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
===========================================
--ron