Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Seperate Name, Street Address & City, State Zip
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 formula =RIGHT(A1,5) assuming your data is in COL A and copying down State by =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... "anna f" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Seperate Name, Street Address & City, State Zip
On Tue, 7 Apr 2009 19:20:01 -0700, anna f
wrote: 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! Since you don't post any examples of the specific formats of these strings, the advice can only be general. But you would have to determine how you can unambiguously separate the different parameters. Using only the information you have posted: The zipcode is probably always the last entry, and could be five digits, nine digits, or 5-4 digits. The state probably precedes the zip code and is either a two letter abbreviation or a one or two word string. The city precedes the state and, if there is no separator between it and the address, could possibly be determined by doing a lookup on the zip code (web 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 the last name from the street address. If you post some real examples, you might obtain more specific information. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Seperate Name, Street Address & City, State Zip
Ron-
Thanks for this information. An example of this is Jane W Doe 1234 Anyplace Street Minneapolis MN 55443-2913 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! "Ron Rosenfeld" wrote: On Tue, 7 Apr 2009 19:20:01 -0700, anna f wrote: 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! Since you don't post any examples of the specific formats of these strings, the advice can only be general. But you would have to determine how you can unambiguously separate the different parameters. Using only the information you have posted: The zipcode is probably always the last entry, and could be five digits, nine digits, or 5-4 digits. The state probably precedes the zip code and is either a two letter abbreviation or a one or two word string. The city precedes the state and, if there is no separator between it and the address, could possibly be determined by doing a lookup on the zip code (web 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 the last name from the street address. If you post some real examples, you might obtain more specific information. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Seperate Name, Street Address & City, State Zip
On Wed, 8 Apr 2009 06:11:01 -0700, anna f
wrote: Ron- Thanks for this information. An example of this is Jane W Doe 1234 Anyplace Street Minneapolis MN 55443-2913 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! The attached macro might help. Important notes: The macro requires an Internet connection which it uses to perform a reverse lookup on the zip code. If the city returned by the web query does not match the word or words in the string preceding the state abbreviation, then it flags an error. The street address is assumed to begin with 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I merge 4 excel columns to 1 column (street,city,st,zip | Excel Discussion (Misc queries) | |||
City State Zip | Excel Discussion (Misc queries) | |||
City State Zip | Excel Worksheet Functions | |||
separate an address street/city,state,zip within a cell? | Excel Worksheet Functions | |||
Splitting City State Zip | Excel Worksheet Functions |