On Thu, 02 Aug 2007 09:07:32 -0400, Ron Rosenfeld
wrote:

On Wed, 01 Aug 2007 20:24:06 -0700, wrote:

hi,

i have a string containing an address/town/zip code and want to split
them in seperate columns.
example:
123 ocean rd smalltown 2134
into:
123 ocean rd | smalltown | 2134

i don't know to do the first split as its position varies but the
street always ends with rd, st, ave. can that be of any help? can
someone help me?

regards
mordy

I would use UDF's involving regular expressions. Then you can add as many
street type abbreviations as you wish.

In the functions below, StrAbbrev is a named cell containing a pipe-delimited
list of your desired street name abbreviations. For example:

rd|st|av|ave|wy|dr|ln

Do NOT include the leading or trailing <space as that is accounted for in the
basic function. Also accounted for, in the basic function, is an optional dot
(.).

Street Address:

=resub(A1,"(.*"&StrAbbrev&".?)\s.*","$1")

City:

=resub(A1,".*\s("&StrAbbrev&")\.?\s(.*)\s\d+-?\d+$","$2")

Zip:

=resub(A1,".*\s(\d+-?\d+$)","$1")

Since you showed a 4 digit zip code in your example, and since that is not
standard for the US, the zip code part of the regex may need to be tweaked. As
written, it consists of any series of digits that may or may not include a dash
(-), begins with a <space and ends at the end of the line.

To use these UDF's you must enter them into a regular VBA module.

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer Window.

Insert/Module and paste the code below into the window that opens.

========================================
Option Explicit
Function RESub(str As String, SrchFor As String, ReplWith As String) As String
Dim objRegExp As RegExp

Set objRegExp = New RegExp
objRegExp.Pattern = SrchFor
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

RESub = objRegExp.Replace(str, ReplWith)

End Function
===============================================
--ron

I omitted one VERY important instruction in editing the above.

After entering the UDF into the module, select Tools/References and check the
entry for "Mocrosoft VBScript Regular Expressions 5.5"


--ron