thanks a lot guys. this works well for me. i added an if loop to test
the regex first so that it returns an empty field if no match is found
instead of the original string.
If objRegExp.Test(str) = True Then
RESub = objRegExp.Replace(str, ReplWith)
Else
RESub = ""
End If
mordy
On Aug 3, 2:20 am, Ron Rosenfeld wrote:
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 astringcontaining an address/town/zip code and want tosplit
them in seperate columns.
example:
123 ocean rd smalltown 2134
into:
123 ocean rd | smalltown | 2134
i don't know to do the firstsplitas 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 AsString, SrchFor AsString, ReplWith AsString) AsString
Dim objRegExp As RegExp
SetobjRegExp = 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- Hide quoted text -
- Show quoted text -