can we be friends, im a loner
anna wrote:
Seperate Name, Street Address & City, State Zip
07-Apr-09
Hello!
I have thousands of entries in Excel where there is a First and Last Name,
followed by a street address, City State and Zip all in one cell. I'd like to
do a mail merge mailing, therefore I need to seperate the data. Does anyone
know how I can accomplish all of these tasks? Or at least part of these
tasks, so I don't have to manually extract the data.
- The address usually begins with a number (Not those in the cases of a P.O
Box
- There isn't a comma or any other delimiter between City, State or Zip.
Your help is greatly appreciated!! Many thanks!
Previous Posts In This Thread:
On 07 April 2009 22:20
anna wrote:
Seperate Name, Street Address & City, State Zip
Hello!
I have thousands of entries in Excel where there is a First and Last Name,
followed by a street address, City State and Zip all in one cell. I'd like to
do a mail merge mailing, therefore I need to seperate the data. Does anyone
know how I can accomplish all of these tasks? Or at least part of these
tasks, so I don't have to manually extract the data.
- The address usually begins with a number (Not those in the cases of a P.O
Box
- There isn't a comma or any other delimiter between City, State or Zip.
Your help is greatly appreciated!! Many thanks!
On 07 April 2009 23:07
to_sAAheelo wrote:
Hope there is a space between each segment of information...
Hope there is a space between each segment of information..
If yes then use Data | Text to table and use space as the delimiter..
If not then all you can hope (there has to be something to hook on) is to
get out the Zip and State assuming they are 5 digits and two letter
abbreviation respectivele..
You can get that
Zip by the formul
=RIGHT(A1,5)
assuming your data is in COL A and copying dow
State b
=MID(A1,LEN(A1)-6,2
Try to paste 3-4 rows so that we can have some idea.
------------------------------------
Pl. click ''''Yes'''' if this was helpful..
:
On 08 April 2009 07:35
Ron Rosenfeld wrote:
Re: Seperate Name, Street Address & City, State Zip
On Tue, 7 Apr 2009 19:20:01 -0700, anna f <
[email protected]
wrote
Since you don't post any examples of the specific formats of these strings, th
advice can only be general
But you would have to determine how you can unambiguously separate th
different parameters
Using only the information you have posted
The zipcode is probably always the last entry, and could be five digits, nin
digits, or 5-4 digits
The state probably precedes the zip code and is either a two lette
abbreviation or a one or two word string
The city precedes the state and, if there is no separator between it and th
address, could possibly be determined by doing a lookup on the zip code (we
query). If there is a comma, that could be used
The first name is probably the first word in the string
Depending on the separators, it may or may not be possible to separate out th
last name from the street address
If you post some real examples, you might obtain more specific information
--ron
On 08 April 2009 09:11
anna wrote:
Ron- Thanks for this information.
Ron-
Thanks for this information.
An example of this is Jane W Doe 1234 Anyplace Street Minneapolis MN
55443-291
I know that I can pull zip and state over because as you mentioned they are
same number of spaces. Would there be anyway to pull the city name or
address?
Thanks!
:
On 08 April 2009 16:04
Ron Rosenfeld wrote:
Re: Seperate Name, Street Address & City, State Zip
On Wed, 8 Apr 2009 06:11:01 -0700, anna f <
[email protected]
wrote
The attached macro might help
Important notes
The macro requires an Internet connection which it uses to perform a revers
lookup on the zip code. If the city returned by the web query does not matc
the word or words in the string preceding the state abbreviation, then it flag
an error
The street address is assumed to begin wit
a number
PO
P.O.
and end with the city. Other formats may be able to be included.
The last name is assumed to be the word prior to the street address
The first name is assumed to be the first word OR it may be the first two words
if they are separated by an ampersand (&).
The middle name(s)/initial(s) are the word or words between the first and last
names.
These rules will not work perfectly, but may be helpful.
As written, the macro operates on your Selected cells, and puts the results of
its operation into the columns to the right of Selection.
It may be possible to make the RevZipLookup macro more efficient. I merely
recorded a macro as a executed a Web Query, and then made a few modifications
in it.
Please read the notes and comments within the macro for more information.
In particular, note that a reference must be set to Microsoft VBScript Regular
Expressions 5.5. You will find this by selecting Tools/References from the
VBEditor main menu bar.
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), first select the range of strings to be parsed.
Then <alt-F8> opens the macro dialog box. Select the macro by name
(ParseString), and <RUN>.
===================================================
Option Explicit
Sub ParseString()
Dim c As Range
Dim str As String
Dim sCity As String
Dim sMiddle As String
For Each c In Selection
'clear old data
c.Offset(0, 1).Resize(Selection.Rows.Count, 8).ClearContents
str = Application.WorksheetFunction.Trim(c.Value)
'get state
c.Offset(0, 6).Value = RegexMid(str, "\b[A-Z]{2}\b", -1)
'get zip code
c.Offset(0, 7).NumberFormat = "@"
c.Offset(0, 7).Value = RegexMid(str, "\S+$")
'get city from ZipCode query and compare with string
RevZipLookup (c.Offset(0, 7).Text)
sCity = Range("$Z$3").Value
Select Case InStrRev(str, sCity)
Case Is = 0
c.Offset(0, 8).Value = "City/Zip Mismatch: " & sCity
c.Offset(0, 8).Font.Bold = True
Case Else
If Mid(str, InStrRev(str, sCity) + Len(sCity) + 1, 2) = _
c.Offset(0, 6).Value Then
c.Offset(0, 5).Value = sCity
Else
c.Offset(0, 8).Value = "City/Zip Mismatch: " & sCity
c.Offset(0, 8).Font.Bold = True
End If
End Select
'get street. Must start with a number, PO or P.O.
c.Offset(0, 4).Value = RegexMid _
(str, "(PO|P\.O\.|\d+).*(?=\s+" & sCity & ")")
'get Last Name = the word prior to street
'this will omit multiple word last names
c.Offset(0, 3).Value = RegexMid _
(str, "\S+(?=\s+" & c.Offset(0, 4).Value & ")")
'get first name. Will accept two first names
'separated by ampersand
c.Offset(0, 1).Value = RegexMid(str, "^\w+(\s+&\s+\w+)?")
'get middle names/initials by removing everything else
sMiddle = Trim(Left(str, -1 + InStr(str, c.Offset(0, 3).Value)))
c.Offset(0, 2).Value = _
Trim(Replace(sMiddle, c.Offset(0, 1).Value, ""))
Next c
Selection.CurrentRegion.Columns.AutoFit
End Sub
Function RegexMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False) _
As Variant 'Variant as value may be string or array
'Requires setting reference (see Tools/References at top menu
'to Microsoft VBScript Regular Expressions 5.5
'Index -- negative values return groups counting from end of string
Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection
Dim i As Long 'counter
Dim T() As String 'container for array results
' Create a regular expression object.
Set objRegExp = New RegExp
'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern
' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive
'Set global applicability.
objRegExp.Global = True
'Set multiline
objRegExp.MultiLine = MultiLin
'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then
'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.
On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim T(1 To UBound(Index))
For i = 1 To UBound(Index)
T(i) = colMatches(IIf(Index(i) > 0, Index(i) - 1, Index(i) _
+ colMatches.Count))
Next i
RegexMid = T()
Else
RegexMid = CStr(colMatches(IIf(Index > 0, Index - 1, Index + _
colMatches.Count)))
If IsEmpty(RegexMid) Then RegexMid = ""
End If
On Error GoTo 0 'reset error handler
Else
RegexMid = ""
End If
End Function
Sub RevZipLookup(ZipCode As String)
Dim Zip5 As String
Zip5 = RegexMid(ZipCode, "\d{5}")
'set to an invisible place on the worksheet
'when retrieving data, the city will be in row 3
'so for $Z$1, the city will be in $Z$3
Dim rDest As Object
Set rDest = Range("$Z$1")
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;
http://www.zipinfo.com/cgi-local/zipsrch.exe?zip=" _
& Zip5 & "&Go=Go", _
Destination:=rDest)
.Name = "zipsrch.exe?zip=04667&Go=Go_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
==================================================
--ron
EggHeadCafe - Software Developer Portal of Choice
HTML Entities Class
http://www.eggheadcafe.com/tutorial...74-9130-3210625e675c/html-entities-class.aspx