Friday, October 21, 2011

Excel Parse CityStateZip Functions

Excel How to:  Parse City-State-Zip fields with an Excel User-Defined functions (UDF). A single cell, such as "Boise, ID 83703" can be parsed into individual fields. A variety of city-state-zip formats, including Canadian, are supported.  These are really smart parsing routines. This article has been streamlined and re-published on this date, with newer versions of the Macro code.

Related Article:
Keyliner: CityStateZip DLL CSZ Class Library - a more sophisticated routine

Parsing city-state-zip fields is complicated because the data can be in a variety of different styles and formats. Consider these examples:

Boise ID 83703
Boise, ID 83703-1111
Boise, ID 83703 1111
Boise,ID 83703
Boise ID 837031111
Post Falls, ID 83703 (multiple-word city)
Post _ _ _Falls, ID _ _ _83703 (embedded, redundant spaces, punctuation)
Salt Lake City UT 12345
Vancouver BC V1B-101 (Canadian)
Vancouver Park BC V1B 101



The Method:
With the code referenced in this article, you can build your own Excel functions.  For example, you are probably familiar with the =Sum() function.  With Excel's macro language, you can write your own function, such as "=ReturnCityName()".

Even with no macro experience, can take the code from this article, add it to your spreadsheet, and use the functions, without having to write or debug the routines.  You should be able to download, apply, and use these routines in about 5 minutes.


Overview:

The macros were written as Excel UDF's (Excel User-defined functions), where you can create your own Excel keywords.  Once installed, the macros can be applied and used like any other function in Excel.  Today, I used these very routines to parse a 200,000 row spreadsheet.  All of this logic happens in under 500 lines of code.

Main Function Names
=ReturnCityName()
=ReturnState()
=ReturnZipCode() Returns entire zipcode, punctuated

=ReturnZipCode5()
=ReturnZipCode4()
=SuperTrim()  (Trims extra leading, trailing and internal spaces)
=ReturnLastWord()


In this illustration, note the single cell with a city-state-zip in cell A5. 
The formula in cell B5 demonstrates the "ReturnCityName".  Other cells show a parsed State and Zipcode.




The routines work by first identifying the zipcode, then backing up, looking for the StateCode. Anything before the State/Province Code is considered a City name. If a postalcode is not found, it looks for a state/province code. The code can return either the string-values or the numeric positions.


See this article for a tutorial on how to build and use User-Defined Excel Functions or you can follow these step-by-step instructions and use the code as-is. 

What it handles
These routines correctly process City-State-Zip (CSZ) strings with these general characteristics:
  • Any type of US or Canadian Postal Code
  • Including ZipCode-5 digits and Zip4
  • Zipcodes can be with or without punctuation
  • City-names can be one or more words
  • State-codes (ID, NY, BC, B.C., ONT), etc.
  • State Names (New York, North Dakota) are supported, if comma-delimited

What it does not handle
With multiple-word state names ('North Dakota', 'British Columbia'), you must have a comma after the city name.

Lincoln City, North Dakota 83700-1234

Single-word state-codes ('ND', 'BC', 'D.C.', 'Idaho', 'Missouri') are handled properly with or without a comma.

Canadian Postal Codes are a mystery to me and forgive me for not knowing them but I believe they are properly accounted for. The code can be punctuated with a hyphen or a space (K1A-1B2 or K01 1B2). The Province Name must follow the state-name rules described above (As an aside, the postal-code is identified by a numeric digit in the 2nd and 7th position, with a space or a hyphen in the middle. The last digit must be numeric in order to detect a US or Canadian postal code.


Installation and Use
Source-code can be downloaded from Keyliner's public GDrive site.  You are welcome to use this code in your personal or commercial projects.

1.  Download two files from this link:
(You do not need to register as a user to download.  Highlight the files, then click the 'Download' button on the top ribbon-bar)

Download Keyliner Excel Macros

Download two files; both are required:
A210_ParseCityStateZip.bas
A800_UtilStrings.bas


2. Save the downloaded files to any directory, such as

C:\Data\Source\CommonVB\A210_ParseCityStateZip.bas
C:\Data\Source\CommonVB\A800_UtilStrings.bas


3. Launch Excel and Enable Excel Macros:

Microsoft considers all macros a security risk and this can be a nuisance.
Do the following to enable the macros.  This is a one-time step for all macros.
  • Launch Excel; click the Orb (formerly the File Menu); choose Excel Options
  • Click Trust Center, Trust Center Settings
  • Macro Settings
  • Enable All Macros
  • Older versions of Office have similar settings.

4.  Open the sheet where you need to parse city-state-zip
or open a blank worksheet to test the routines.  In the Excel sheet, import the VB code using these standard Excel steps:

a.  Click the "orb", Excel Options 
b.  On the left, choose "Popular"
c.  Click [x] Show Developer tab in the ribbon and return to the sheet


5.  Start the VBA Macro Editor by pressing Alt-F11.
The VBA Macro Editor will open in a new window.


6. On the tree-diagram, illustrated below, select your sheet ("Book1"), then:
  • Other-Mouse-Click (Book1),
  • Choose "Import File"
  • Browse and select the previously-saved ".bas" module,
    e.g. C:\Data\Source\CommonVB\A210_ParseCityStateZip.bas
  • You will see the code appear under the "Modules" folder (not illustrated)
  • Repeat this step and import
    C:\Data\Source\CommonVB\A800_UtilString.bas  (the second macro file)

7. Close or minimize the Visual Basic editor by clicking the editor's "X".  This returns you to the sheet and the macros are ready to use. 

Note: All the macro editor did was "attach" the code.  You can optionally choose to view the programming code by using the editor. 

Also, the macros will save with the sheet, but only if you follow the Office 2007/2010 Save warning, documented near the end of this article.



Testing:

A. In Cell A1, type an example City-State-Zipcode for testing. Press Enter when done:

example:  Boise, ID 83703-1234


B.  In cell B1, type this formula:

=ReturnZipCode(A1)

Results: The Zipcode should return.

Try these other functions: 
"=ReturnState()"
"=ReturnCityName()"
"=ReturnZipCode5()"

Possible Errors:
If you get a "#NAME?" when typing =ReturnZipCode, the library was not linked in properly or was not linked to this particular sheet (the macros are only available to the sheets where they were copied).  Review the steps above.  If a routine, such as "SuperTrim" or "FindLastWordPos" fails, confirm you linked in the A800_UtilStrings library.



Excel 2007/2010 Save Warning:

When Excel 2007/2010 saves the sheet, it will save it as an ".XLSX" and will strip the macros out as it saves! (Microsoft is doing this as a security precaution.)



When prompted: 'Do you want to save as a macro-safe) workbook', click "Yes"  or optionally, do a File-Save-As, and choose "Excel Macro-enabled Workbook (*.xlsm).


Descriptions
This article does not explain how this code was developed; that has been left for your study. But the provided code should work well with any version of Microsoft Excel, from version 97 onward. This code has been tested with Office 2007. Converting the code to VB6 or VB.Net should be nearly transparent. All numeric position/counters are base-1, per VisualBasic's norm.

Functions within the A800_UtilStrings routine are of particular interest:
=SuperTrim()
Trims all duplicate interior, leading and trailing spaces. This function is potentially helpful in a wide variety of situations, above and beyond CSZ parsing.

=FindLastSpacePos()
Returns the position of the last space, base-1; returns a numeric value

=ReturnFirstWord()
=ReturnLastWord()
Returns the first and last word in a string; properly trimmed.

=FindStatePos()
Returns the State-code/State-name's numeric position in the string; base-1. Other functions return similar values:

=ReturnCityName()
=ReturnState()
=ReturnZipCode()
=ReturnZipCode4 ()

=GetCSZPositions

Returns 4 numeric values, showing the position of each of the CSZ's subparts. See the comments near the top of the code for a complete description.

Other functions, such as =FindState, return the numeric positions and are provided as a convenience.

Comments welcome.

Link
Related articles:
Excel Macros - Formatting Phone Numbers
Excel Macros to Parse City State ZipCodes
Excel Macros to return First Word, Last Word, Supertrim, Count Words
Writing Excel UDF functions
Excel VLookup - A complete Tutorial
Excel Coloring Alternate Rows
Excel Importing Leading Zeroes

Thursday, October 20, 2011

Excel Code: VB ReturnFirst/Last and SuperTrim

Reference: These Excel/VB macros for ReturnFirstWord, ReturnLastWord and SuperTrim are very handy.  Download these routines, free of charge, without registration, from Keyliner's Public GDrive site.  Details on how to use these functions can be found in other Keyliner articles.  This article was streamlined and re-published.

Contents:
=ReturnFirstWord
=ReturnLastWord

=FindLastSpacePosition (numeric result)
=SuperTrim; Trims multiple redundant leading, trailing *and* interior spaces

=CountWords

These Excel UDF Macros (User Defined Functions) can be downloaded and attached to a speadsheet in a matter of minutes.  Once in place, they can be used in any Excel formula.  Other Keyliner routines, such as Excel CityStateZip Parse Functions use these modules.
 

Installation and Use

Source-code can be downloaded from the Keyliner public GDrive site  (formerly, this article listed the code, but the code became too ungainly to post in a blog).  You are welcome to use this code in your personal or commercial projects.

1.  Download Excel Macros from link:
(You do not need to register as a user to download)
Keyliner' Public GDrive


Download:
A800_UtilStrings.bas


2. Save the downloaded files to any directory, such as:
C:\Data\Source\CommonVB\A800_UtilStrings.bas


3. Launch Excel and Enable Excel Macros.

Microsoft considers all macros a security risk and this can be a nuisance.
Do the following to enable the macros.
  • Launch Excel; click the Orb (formerly the File Menu); choose Excel Options
  • Click Trust Center, Trust Center Settings
  • Macro Settings
  • Enable All Macros
  • Older versions of Office have similar settings.

4.  Launch Excel to a blank sheet and import the VB code using these standard Excel steps:

Click the "orb", Excel Options. 

On the left, choose "Popular"
Click [x] Show Developer tab in the ribbon

5.  Press Alt-F11 (Starts the VBA / Macro Editor)

6. On the tree-diagram, illustrated below, select your sheet ("Book1"), then:
  • Other-Mouse-Click (Book1),
  • Choose "Import File"
  • Browse and select the previously-saved module, e.g. C:\Data\Source\CommonVB\A800_UtilStrings.bas
  • You will see the code appear under the "Modules" folder (not yet in the illustration below but will be when you complete these steps)
7. Close the Visual Basic editor and return the original sheet; clicking the big "X".




Testing:

A. In Cell A1, type an example string, with leading, trailing and extra internal spaces. Press Enter when done:

example: "  Now is    the time "


B.  In cell B1, type this formula:

=SuperTrim(A1)

Results:  "Now is the time"  (with interior spaces trimmed)

C.  In cell B2, type this formula:

=ReturnLastWord(A1)


Results:  "time"


Related Keyliner Articles:
Excel UDF (User Defined Functions)

Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
Using VBA to Send Email

Wednesday, October 19, 2011

Salutation List Reference

Reference list for Salutations (English)

Prefixes, salutations or honorifics, such as "Dear Rt Hon Mr." John Q. Smith,

I had a request to parse prefix Salutations from a database of names and had troubles finding a comprehensive list.   Below is that list, along with links on how I did the parsing.

Shorter, common salutations are punctuated with and without periods while longer ones, by convention, are listed without punctuation (e.g. Rt Hon Mr  vs  Rt. Hon. Mr.). The referenced parsing routines use this list and the logic is easily changed for additional entries.

Leave an un-registered comment if you have additional salutations.  I purposely dropped the Pope and the Queen, figuring we would never need to write to them using a database and mail-merge.

Related articles:
Parsing First and Last Names in Excel
Parsing Salutation in Excel (Parses this list)

Parsing City State Zipcodes in Excel
Excel User-defined formulas

English Prefixed Salutations

Admiral
Air Cdre
Air Commodore
Air Marshal
Amb
Amb.
Ambassador
Baron
Brig
Brig Gen
Brig General
Brig.
Brigadier
Brigadier General
Bro
Bro.
Brother
Canon
Capt
Capt.
Captain
Cardinal
Cdr
Cdr.
Chief
Chief Warrant Officer
Cik
Cik.
Cmdr
Cmdr.
Col
Col.
Colonel
Commandant
Commander
Commissioner
Commodore
Congressman
Corporal
Cpl
Cpl.
Cpt.
Cpt
Dr
Dr.
Dr and Mrs
Dr. and Mrs.
Dr & Mrs
Dr & Mrs.
Father
First Lieutenant
First Officer
Flt Lieut
Fr.
Gen
Gen.
Generaal
General
Gov.
Governor
Group Captain
Grp Capt
Hon
Hon.
Honorable
Hr
Hr.
Judge
Justice
Lieut
Lieut Cdr
Lieut Col
Lieut Gen
Lieut.
Lieutenant Colonel
Lieutenant General
Lt Col
Lt Col.
Lt.
Maj Gen
Maj.
Major
Major General
Master Warrant Officer
Mayor
Miss
Mr
Mr.
Mrs
Mrs.
Ms
Ms.
Pr
Pr.
Pastor
President
Private
Prof
Prof Dr
Prof Dr.
Prof Sir
Prof.
Professor
Pvt
Pvt.
Rabbi
Rep.
Representative
Rev
Rev Canon
Rev Dr
Rev Mother
Rev.
Reverend
Rt Hon
Rt Hon Mr
Rt Hon Mr.
Rt Hon Ms
Rt Hon Ms.
Rt Hon Mrs
Rt Hon Mrs.
Rt Hon.
Rva
Sec.
Second Lieutenant
Secretary
Sen
Sen.
Senator
Sergeant
Sergeant
Sgt.
Sister
Sr
Sr D
Sr.
Sra
Srta
Sultan
Tan Sri
Tan Sri Dato
The Hon Dr
The Hon Dr.
The Hon Justice
The Hon Miss
The Hon Mr
The Hon Mr.
The Hon Mrs
The Hon Mrs.
The Hon Ms
The Hon Ms.
The Hon Sir
The Honorable
The Most Reverend
The Reverend
The Venerable
The Very Rev
The Very Rev.
The Very Reverend
Vice Admiral
Warrant Officer

Sunday, October 16, 2011

Excel - Macro Editor Removes Spaces

How To: The Excel 2007 Macro Editor is automatically removing spaces between words while typing code.

Problem: 
While typing a new function name or other text in the Excel Visual Basic Macro, the editor  is removing the spaces between words as you type (automatically, unexpectedly, auto removing, auto-correcting).  For example, you may be attempting to type a new function name:

Public Function MyFunction()
but the editor will correct the text as "PublicFunctionMyFunction()"



Note: You have also likely installed Microsoft Visual Studio 2010 sometime previously.  This product introduced a Load Test Report Add-in that is apparently causing this bug.  This article is current as-of Office 2007, SP2.  I suspect Office 2010 exhibits the same issue.


Solution:

1.   De-activate the Excel "Load Test Report Addin"  (add-in) using these steps:

a.  Click the Excel "orb" (File menu)
b.  Click the bottom-right button, "Excel Options"
c.  Click left-side "Add-Ins" menu
d.  In bottom, center pull-down, Choose "Manage" "COM Add-ins".  Click Go


2.  Uncheck [ ] "Load Test Report Addin"




3.  Press Alt-F11 to return to the Macro editor.  The problem should be resolved.

Making this change is permanent (for this workstation) and the change sticks for the current worksheet and all new and existing sheets.

Keywords:
VB VBA Editor auto-delete auto-deletes auto delete spaces at the end of the line, spaces as typing, spaces while typing, removes spaces, collapses spaces

Tuesday, October 11, 2011

Excel - Enable Macro Developer Options

How To:  Excel Developer Options - How to enable Macro and Macro Editing for Excel 2007/2010/2013.  This is an excerpt from a previous article

1. Launch Excel and Enable Excel Macros.

Microsoft considers all macros a security risk and this can be a nuisance.
Do the following to enable the macros.  This is a one-time step for all macros.

  • Launch Excel; click the Orb (formerly the File Menu); choose Excel Options
  • Click Trust Center, Trust Center Settings
  • Macro Settings
  • Enable All Macros
  • Older versions of Office have similar settings.

2.  Expose Developer Options

Office 2008
a.  Click the "orb" (or File Menu), Excel Options 
b.  On the left, choose "Popular"
c.  Click [x] Show Developer tab in the ribbon
d.  Click OK and return to the sheet

Office 2010
a.  Click File, Excel Options, "Customize Ribbon"
b.  In the second-column ("Customize Ribbon: Main Tabs"), check [x] Developer
c.  Click OK and return to the sheet


3.  Enable Option Explicit Variables
In Office 2010 and above:
From any Blank Sheet, press Alt-F11 to open the VBA Editor

a.   Select Menu "Tools", Options, [Editor Tab],
b.  Check [x] Require Variable Declaration

This replaces the "Option Explicit" line you used to have to type in macros -- forcing you to declare variables before using - a recommended practice.

Related Posts:
Parsing First and Last Names in Excel
Parsing City State Zip in Excel
SuperTrim in Excel
User Defined Functions in Excel