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.


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
=ReturnZipCode() Returns entire zipcode, punctuated

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

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:

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


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.


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:


Results: The Zipcode should return.

Try these other functions: 

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).

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:
Trims all duplicate interior, leading and trailing spaces. This function is potentially helpful in a wide variety of situations, above and beyond CSZ parsing.

Returns the position of the last space, base-1; returns a numeric value

Returns the first and last word in a string; properly trimmed.

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

=ReturnZipCode4 ()


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.

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.


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


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


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

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".


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:


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

C.  In cell B2, type this formula:


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

Air Cdre
Air Commodore
Air Marshal
Brig Gen
Brig General
Brigadier General
Chief Warrant Officer
Dr and Mrs
Dr. and Mrs.
Dr & Mrs
Dr & Mrs.
First Lieutenant
First Officer
Flt Lieut
Group Captain
Grp Capt
Lieut Cdr
Lieut Col
Lieut Gen
Lieutenant Colonel
Lieutenant General
Lt Col
Lt Col.
Maj Gen
Major General
Master Warrant Officer
Prof Dr
Prof Dr.
Prof Sir
Rev Canon
Rev Dr
Rev Mother
Rt Hon
Rt Hon Mr
Rt Hon Mr.
Rt Hon Ms
Rt Hon Ms.
Rt Hon Mrs
Rt Hon Mrs.
Rt Hon.
Second Lieutenant
Sr D
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.

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.


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.

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

Tuesday, August 23, 2011

PaintShop Pro Fuzzy Illustration Text

How: Using PaintShop Pro to write crisp text. Default PSP text is fuzzy, indistinct. This article describes how to fix this problem.

When using PaintShop Pro (X4 and older versions) for illustrative text, you will be disappointed in the quality. Small text will be especially indistinct and fuzzy. Larger font sizes do not exhibit these problems.

For example, here is a screen shot that needed additional text to help with the illustration. The upper-right shows the default PSP text. The paragraph below shows the improved text recommended by this article.


By design, the program composes fonts in two parts, using an outline and a fill. While this works great with larger sized fonts, it falls apart when the font is 14 points or smaller. PSP likes to take a font and color the 'stroke-width' differently than the 'fill.' As the font gets smaller, the outline competes with the fill and everything becomes indistinct. Anti-aliasing also introduces problems at these finer point sizes.

Setting the Font:

For best results, I recommend these settings, for all point sizes.
In summary, don't use the outlined "stroke width" and flip the foreground colors:

1. Select the Text tool "A" from the toolbar menu.

2. On the top toolbar, set the "Stroke Width" to 0.0
(you can leave this setting permanently - the measurement sticks with the font tool).

3. Turn Anti-aliasing off for fonts 14 points or smaller (see Anti-Alias on top button bar, near stroke width).

4. Most importantly, switch the foreground and background colors.

This is the key:
Set the background color to the color you want the text to be by "flipping" the foreground and background colors on the materials pallet.  The background color will become the text color when the stroke-width is set to 0.0.  If the Material Pallet is not visible, click View, Pallets, Materials.

Illustrated below, the text color is set to red.

In practice, flipping the colors is a nuisance. You'll flip the colors, type your text, then return to other drawing tools -- where you will find the colors are wrong and you will have to flip them again.

Related Articles:
Windows 7 and Paint Shop Pro Not Working
Using PSP to Crop Images respecting Aspect Ratios

Friday, July 22, 2011

PSP X3 Bug - Bottom Pixels being Cut off

How To: Corel PaintShop Pro X3 (PSP X3) truncates bottom pixels from a selection when pasted.

Some installations of PaintShop Pro PSP X3 are truncating the bottom row of pixels when pasting the selection onto another graphic or other layer.

Vendor Solution:

As of 2011.07.22, This solution is not part of Service Pack 4 and the patch must be applied separately. This patch will not arrive via automatic updates. If Service Pack 5 (5+) is released, this patch may be obsolete and should not be used.

Important note: PSP Service Pack 4 (or later) must be applied prior to this patch.

1. Confirm Service Pack version:

Launch PSP X3, Help, About.
Look for this version:
Close PSP prior to applying the patch.

Note: PSP X3 SP4 can be found here.

2. From Corel's site,

Link: 000005465 - pixels along the bottom edge of a selection dropped

Download the FTP file displayed in the article to a known location.

3. Launch and install the downloaded file:

File Details:

This patch does not change the version-number.

Related articles:
PSP X3 hangs
Using PSP to Crop Wallpaper Images (Aspect Ratio)
PSP Fuzzy Text Fix

Article Keywords:
drops / truncates / cuts off / cuts / chops paintshoppro / paintshop pro paint shop pro

Saturday, July 16, 2011

Cleaning Startup Programs

Howto: This article discusses how to use MSConfig to cleanup a computer's startup programs, providing faster boot times, less memory, faster computers, with less junk. This article was written for Windows 7, Vista and XP and was updated on 2011.07.

See also:
Windows 7 Streamline Start Menus (Organizing the Start Menu)
Windows XP/Vista Streamline Start Menus
Cleaning Scareware and other Viruses

Cleaning up Startup Programs

The other day my laptop booted and bugged me about a printer update. During this past two years I have never once wished for a printer update -- how did it know? There is only one explanation: For the past two years, a program that does nothing but look for updates has been running.

Programs like this hog bandwidth and kill boot times.

How is Your PC?

In the lower-right corner, look at the System Tray (you may need to click the arrow to expand it). If you have more than a half-dozen icons, I'd bet most are pointless and seldom, if ever, used. Every icon in the system tray represents a program that takes CPU-time and for every one exposed in the tray, there are probably a half-dozen hidden ones.

* Are you being bugged about an auto update?
* Does your PC take more than 70 seconds to boot?
* Does your System Tray have 8, 10 or more icons?

Here are all the icons on my desktop computer. I consider this a healthy system.

Ask these questions:
  • Does the icon show useful information?
  • Does the icon help in your day-to-day work?
  • Do the icons benefit you?
Sadly, the System Tray represents a fraction of the startup programs a computer must endure. Although some should not or cannot be disabled, most are expendable. This article will help to identify them.

Cleaning Up

To clean up the startup programs, do the following.

1. Click Start, Run, and type "MSConfig"
If the RUN command is not visible, see this article.
(See also the recent article on another Microsoft utility: AutoRuns)

2. Select the "Startup" tab at the top of the screen.

3. Uncheck items that you think are safe to disable, after reading the comments below.

(Click image for larger view; click right-x to return)

Items marked with a 'check' run when the computer boots and generally stay running even though you may not see them. Unchecked items are disabled (but remain installed). Most of the computers I've met have 20 to 30 items in the list with most enabled. Oink.

MSConfig does not offer help on what is safe to disable but you can get a better idea by widening the Command column (drag the column-width header). Often, just by the file-path, you can tell what the program is.

For instance, my computer had two programs running in an HP directory: "HPWuschd." and "HPcmpmgr". The first fetched updates (schd = scheduled) but what about the second? Google to the rescue.

After searching "hpcmpmgr", I found what the program does. "hpcmpmgr.exe is the main process belonging to the HP Component Manager, and is important for their multimedia products." This sounds important. It continued with, "This program is a non-essential process but should not be terminated unless suspected of causing problems." Reading further, the program also checks for Printer-driver updates. Ah-ha! There are two programs doing the same thing!

Ignore Lawyer-induced Language:

When you look up any process in Google, they will always waffle and say, "leave it running unless it is causing problems." Pay no attention to wishy-washy, lawyer-induced language.

Instead, latch onto the 'non-essential' phrase. If you see that kind of terminology you can almost guarantee the program is optional and you can use MSConfig to uncheck it. (for that matter, 'essential' programs can often be taken with a grain of salt.)

After unchecking a program, consider rebooting the computer. Test the device or associated program in question. In my case, I have two HP Printers and an HP Digital Camera; all worked even though these programs were disabled. I now have two less programs in my startup files.
Safety Check:

After unchecking and rebooting, MSCONFIG displays a warning - offering the chance to re-enable these programs. Give the PC a few days and a few reboots before permanently dismissing the warning. Once you click "Don't bug me again," MSConfig won't say a word until you make another change. This is a nice safety feature and it allows you to easily un-do what you did.

Important Sounding, but not really

Of the 21 programs in my startup list, I disabled 14 of them.
Here is a taste on what I found; each of these were disabled:

- Acrobat Reader Updater
- Acrobat PreLoader
- Adobe ARM
- Adobe Reader and Acrobat Manager (ARM - Auto-update)
- Adobe Reader Speed Launcher
- Corel Photo-Downloader
- Standby Service (Standby.exe Corel)
- DellSupportCenter
- Java(TM) Platform SE Auto Updater
- Sun Java Update Scheduler
- NVidia Media Center Library
- Dell QuickSet
- (Quicken) QTask
- Quicken Startup
- RealSched

Cleaning crap like this can easily take 60 seconds or more off boot times and I have worked on dirty machines where I took 5 minutes off boot times.  Here are more comments and examples of things to delete:

- Dell QuickSet (power management software)
Reachable via a Windows control panel

- Tfswctrl (something to do with HP's CDRom burner)
I noted that I can still burn CD's. Nobody seemed to know exactly what this program did. But more interestingly, I retired that old HP CDR last year. It pays to search your startup folders every now and then.

- ISUSPM (Dreamweaver auto-update program)
The odds are next-to-nil that my two-versions-behind copy of Dreamweaver will have an update. Yet every day my computer searched for an update. What a waste.

- Qttask
(Quicken Checkbook bill minder - Pop-up reminders about past-due bills). I don't use this feature but Quicken installed it on the off-chance I might.

- RealSche (RealPlayer Update)
We already know they have an update. They have one every week. I certainly don't need a reminder.

- My DVD movie player had an important-sounding driver in the Startup, "InterVideo WinCinema". This was one of those rare drivers where I couldn't find any information telling me what this program did. Finally, one minor site had enough guts to say "This program is not required." I disabled it a year ago and get this, I can still watch movies on my laptop.

AutoUpdaters - A dime-a-dozen:

Most applications include an update-menu within the program and some of these programs may be several years old and will probably never have an update (think about your DVD Movie player, photo-editors, printers, etc.). Why launch a program each time you boot? For most of these programs, open the application, click Help, "Check for Updates" while others are visible in the control panel. For example, Corel Update, Microsoft Office Update, Acrobat Updater, all can be updated manually.

Opinion: If you disable some auto-updaters, such as Adobe's Acrobat ARM, you will have to manage updates yourself. Periodically, launch Acrobat and select, Help, Update -- saving your computer the startup headaches. For any other auto-updaters, look inside of the program for an "update" menu -- most have them.

Video Drivers:
As an aside, when installing new video drivers, from NVidia or ATI, only install the "base" drivers. For example, here are my recommendations:

When installing, always choose "Custom (Advanced)"

In Components:
[ ] Nvidia Update (recommend checking manually)
[ ] Physx System Software (Check [X] if you are a gamer)
[ ] 3D Vision Controller Driver
[X] Graphics Driver
[ ] 3D Vision Driver
... catching this stuff during a custom install saves you from having to remove them in MSConfig.

What Not to Delete

The laptop's wireless ethernet card probably has a few goodies that you shouldn't mess with. On my computer you can recognize the directory "Intel\Wireless\Bin". You'll also want to leave your virus and spyware scanners as well as your firewall software. Hardware drivers, such as your Touchpad (APOINT.exe), Video drivers (Nvidia, ATI), and Sound drivers should be left alone.

Usually both Video and Sound Drivers put icons in the system tray that are not removable. Some vendors give the option to hide the icon. Other-mouse-click the icon and look for a "don't display in System Tray." You may ask, What if I need to get to the icon?" Visit the control panel; all of your options are there.

If in doubt about what an item is, search the web, using the "startup item's" name to see what other people have discovered. Remember, words like "optional" or "non-essential" are clues.

Here are some of the tasks that I left running:
+ APoint
+ Realtek HD Audio Manager
+ Intel(R) CommonUserInterface (multiple)
+ Acronis Scheduler Helper (Acronis backup requires this)
+ Acronis True Image
+ Corel File Shell Monitor (PSP licensing?)
+ Microsoft Security Client (MSE)
+ Microsoft Intellipoint (Mouse)
+ Microsoft Windows(R) Operating System (Sidebar)
+ Microsoft Windows Operating System (ctfmon.exe)
+ NVidia / ATI drivers
+ IconUtility (Alcor Card Reader)
+ Unknown (Roxio CD Bruner

Your system, and its particular hardware, may have different needs.


Some programs, such as older versions of Microsoft Office, put programs in the Windows "Startup Group" (folder). See Start, Programs, STARTUP to see what is lurking there. Usually they can be deleted. This includes Adobe Acrobat's pre-load, Microsoft's Office Startup, MOM, DAD, Typing Tutor Pre-loaders, etc. I've never found an icon that was required to live in this group. Consider moving the icons to another folder before deleting them, this way you can return them if needed.

Check Occasionally

At least once every few months, especially if you have been installing new software or hardware, look through the list and do some weeding. Somebody is always sneaking something in there. As always, reboot and test. If find something quit working (such as your scanner or camera), launch MSConfig and recheck the box and reboot.


If you are familiar with Regedit, all MSConfig changes live in this structure:
HKLM\Software\Microsoft\Windows\CurrentVersion\Run and \RunOnce.

Windows XP places a subfolder below, where msconfig hides the items you unchecked. Vista tucks them in a different location:
HKLM\Software\Microsoft\Shared Tools\MSConfig\startupREG

If you are skilled with Regedit, you can remove items from here, but MSConfig/Autoruns is easier to use and less dangerous.

Other Steps
Vaguely related to this is your Start Menu. Although cleaning this up does not improve your computer's boot times, it can certainly improve how much you like using the computer. Read the following articles on how to streamline your Start Menu.

Related Articles:
Windows 7 Streamline Start Menus (organizing the Start Menu)
Windows XP/Vista Streamline Start Menus
Disk Cleanup Steps
Optimizing the Swap File
CPU Utilization (AVG)

Saturday, July 9, 2011

WordPerfect - Hanging Indents - Paragraph Headers

Reports and other papers often need Hanging Indented paragraphs or Margin-released headers.

Hanging Indents:

A Hanging Indent takes the first line of the paragraph, leaving it against the margin, while all remaining lines in the paragraph are indented one tab-stop. At the next paragraph break (hard-return), the new paragraph returns to the normal margins.

To build a hanging indented paragraph:
1. Place the cursor in front of the first character in the paragraph.front of the first line.
2. Select menu: Format, Paragraph, Hanging Paragraph (most people use a shortcut: Ctrl-F7). You could also fiddle with the ruler-bar aka the Microsoft Word way, but that takes too much time and effort.

Hanging Indents usually are used to offset "quoted" material, although a double-indented paragraph is a better formatting decision in those cases. In either case, the paragraph, and all of its text, 'wrap as paragraphs should,' staying as one continuous block of text.

Reveal codes will show two codes at the front of the paragraph: an [Hd Left Ind] and [Hd Back Tab].

Some people mistakenly try to use Hanging Indents for section-headers, illustrated above as a Margin-Release Hanging Indent. The trouble with this idea is all other paragraphs in the paper would have to be indented manually, essentially bypassing the margin settings.

Margin-Released Hanging Indents:

If you need what I like to call a "Margin-Released Hanging Indent:"

  • press Shift-Tab to move the cursor one tab-stop to the left of the Margin.
  • Begin typing the "paragraph-header"
  • Press Enter to start a new paragraph along the existing margin

Notice how you can do this with a simple menu/keystroke; there is no need to fiddle with the ruler-bar or margins and all subsequent paragraphs stay along the original margin. "Out-dent" new headers by exception, only as needed.

In Reveal Codes, the margin release is known as a "HD Back Tab" (hard back-tab).

Keep in mind that these types of headers are nothing more than a short paragraph that is shorter than one line. The risk is, at a page-break, they may stay on one-side of the page while the following text jumps to the next page. See this article (WordPerfect - Protect Text Across Page Breaks) for hints on how to handle this.

Related Articles:
WordPerfect - Block Protect Text Across Page Breaks
WordPerfect - Using WP for School Papers - Page Numbering

WordPerfect - Protect Text Across Page Breaks

WordPerfect Howto: Keeping Text together across page-boundaries. Block Protecting text across page-boundaries. Block protecting lists.

A document has a margin-release header (somewhat like a hanging indent) that is approaching a page-boundary. The text below the header crossed the page-break and the header is now orphaned.

Ideally, the heading and at least a sentence or two of its text should stay grouped together on one side of the page-break or the other. Standard "Widows and Orphans" won't work in this example because the header is technically a separate paragraph.

The "improper way":

The improper way to fix this problem is to ram a manual hard-page-break [Ctrl-Enter] above the header, forcing the header and its text to the next page. This solves the immediate problem but becomes a maintenance headache.  If you use a hard-page-break, it may end up in the middle of a page as other text is inserted or deleted; sooner or later, you will be forced to clean up the mess.

  • Manually typing Hard-Page-Breaks [Ctrl-Enter] in any flowing text should be avoided. Manual page-breaks require too much maintenance over the life of the document.
  • Widow and Orphan controls do not apply here because technically, the "header" is a separate paragraph.


"Block-protect" the header and (part of) the next paragraph, locking them together as one object. As the blocked text reaches a page-boundary, it leaps to the next page as one group. If text above is deleted, and enough space becomes available, the entire block automatically moves to the previous page.

  • Highlight the header and at least two sentences into the paragraph. Often, it is easier to highlight the entire next paragraph.
  • Select menu: Format, "Keep Text Together"
  • Select [x] Block Protect

The text-header and next paragraph cross the page-boundary as one entity.

Reveal codes will look like this:

Click image for larger view, click "right-x" to return
  • With a hanging Header, as illustrated above, start highlighting to the Left of the the codes. In other words, highlight left of the HdBack Tab and Bold.
  • Block protect *all* headers and their next paragraphs, even if they are not at risk of crossing a page boundary. This saves you the trouble of chasing them as the document's length changes.
  • If the next paragraphs are "long", block-protect only the first few sentences -- but in general, I tend to block the entire paragraph.

  • Use this same technique to block-protect items in a list.
  • Block protect the entire list (if short), or at least the first several items (if long)
  • Block protect the list, even if it is not at risk of a page-break.

  • Often, as I'm writing long papers, I don't bother protecting the text until near the end -- ignoring all page-breaks, letting the text fall where it may. Then, starting at the top, I go through the document, protecting all headings and lists. The paper will be perfect and it will survive all future edits.

Macro Hint:

Block protecting text is a common occurrence in long papers. Because it is used so frequently, I recommend writing a simple WP macro and assigning it to the never-used F1-Help. With this, you can quickly protect text without wading through the menus.

The "block-protect" macro is short and can be recorded, or you can use this two-line macro:

Application (WordPerfect; "WordPerfect"; Default!; "EN")
BlockProtect (State: On!)

Related Articles:
WordPerfect Page Numbering for School Papers
WordPerfect - Hanging Indents / Paragraph Headers
WordPerfect X5 - A Quick Review

Monday, July 4, 2011

Using WordPerfect for School Papers

This article describes how to use WordPerfect on a normal High-School or College paper. With these steps, your papers will have proper page numbering and pages will always "break" correctly. You will not need to make manual page-breaks nor will you need to delete previously-built page-breaks. In short, the paper will behave no matter what text is inserted or deleted.

These instructions work with all versions of WordPerfect for Windows and was written using WP X5 and still work in the same in version X8. For best results, follow these steps, in this order.

  • Fixing White-Space Behaviors
  • Setting a Default Font
  • Setting default Tab Settings on Ruler Bar
  • Margin Releases
  • Paragraph Widows and Orphans
  • Reveal Codes
  • Draft View
  • Fancy Page Numbering
  • Headers
  • Cover Page
  • Suppressing Page Numbers / Footers
  • Reset Page Number to 1
  • First Page / Duplexed First Page (Odd)

Initial Setup:

Make these one-time recommended changes to WordPerfect's preferences. This makes editing all future documents easier. If you have already made these changes, skip to the Starting a New Paper section.

A. Set the Cursor "Active in text"
This keeps mouse-clicks from inserting tabs in the middle of the document. I have no earthly idea why WP would default the setting the way they did. By turning this off, the word processor will behave more naturally.
  • In Tools, Settings, Display
  • Select the first tab, [Documents]
  • Mark "Active in Text"
  • Click OK and close the preference screens

B. Set a Default Font:
  • From a blank document, select Format, Font.
  • Choose Times New Roman (a True Type font)
  • Size 11 or 12 (11 looks more professional)

  • On the same Font menu, click the bottom "Settings" button.
  • Choose "Set face and point size as a default for all documents"
  • Click OK

Following the standards set by nearly all printed magazines and newspapers, printed material should be a serif-font (Times New Roman) while online material is typically a sans-serif font (Arial).

C. Set optional default tabs:
Set a default tab (ruler-bar) for this and all new documents. By default, tab stops are every 1/2 inch -- which is too wide. Set the tab-settings every 1/4" inch. This also sets a Margin Release. Even if you like tab-settings at 1/2", you should still set the negative -.50 margin release.

Setting Tabs:
  • Format, Styles: Choose "DocumentStyles"
  • Click Edit to open the "Style Editor"
  • Important:  Confirm "[x] Use as default" is checked in the lower-right corner
  • Click the mouse in the "Contents" section
  • Click Format, Line, TabSet

  • Confirm Tab Type = Left
  • Change Tab Position to -0.5 (negative point 5)
  • Click box [x] Repeat every .25 (point 25)
  • Click "Set"
  • Click Close, returning to the Styles Editor.  Ok.

Margin Release Example:

Notice how tab stops are set 1/2" to the left of the Left margin (-.5) -- this is called a "Margin Release." Often, in a paper, a Hanging Header is needed. With this setting, you can quickly type a bolded title that "sticks out past the margin" without having to fiddle with the ruler-bar or with margins.

Click illustration for a larger view; click right-x to return

To use a Margin Release:
  • At the beginning of a paragraph, press Shift-Tab to hang the heading. Press Shift-Tab a second time to jump one more stop to the left.

D. Set Widow and Orphans
(This keeps the first and last line of a paragraph from 'orphaning" at the bottom of a page-break -- it always forces at least two lines before a page-break. No more chasing text that happens to live at the bottom of a page.)

Still in the Styles Editor, select Format, "Keep Text Together"
[x] Prevent first and last lines of paragraphs from being separated across pages" (check)
Click Ok

E. Optionally, set a default Font in the "Default DocumentStyle"
Set the font using the instructions from above. You will note it appears in the default Document Style.  From this screen, you can insert any codes (settings) you need in all future (default) documents.

Confirm the settings.

From the main Styles Editor screen, confirm the Tab Set and Wid/Orph are set in the Reveal-codes 'Contents' window (the order does not matter).

  • Click "OK" to save
  • Click "Yes" when prompted "Apply this style to new documents as they are created."

This completes the default WP preferences.

Starting a new Paper

With the settings above, all new papers begin with a preferred default font, tab settings and Margin-releases. Widows and Orphans are also set. The remaining settings, below, could also be set in the Default/Preferences, but I prefer setting them manually at the document level because papers have different needs. These changes will be "Document" changes (rather than defaults).

When making any Document change, remember WordPerfect makes its changes at the cursor's position, down. In other words, where ever the blinking cursor is, the change takes effect from that point until the end of the document. This is noticeably different (and better) than Microsoft Word -- which makes all of its changes at the "current paragraph".

1. Begin by Exposing Reveal Codes

If you have not already done so, expose WordPerfect's famous Reveal Codes. This makes editing a complicated document easier because you can see all codes, including bolds, underlines, font-changes, footnotes, etc.

Click and drag-up the reveal-codes pane (lower-right corner), as illustrated, exposing a half-inch or so:

2. Set Draft View (recommended)

Although WordPerfect can edit documents in a full "What-You-See is What you Get" mode, it is easier to edit in Draft mode. This way, you don't have to scroll past white-space and Headers, Footers and Footnotes don't get in the way.
  • From the top menu, select View, "Draft"

The rest of this document assumes "Draft view"

3. Set Page Numbering for all Pages

The easy way to set page numbering is with menu - Format, Page, "Numbering".  Be sure the cursor is at the top of the document when making this change.  This type of page-numbering shows at the bottom center of each page.

If a more complicated page indicator is needed, use a Page Footer.
For example, this footer shows a horizontal line, your name, a page number and the Course title:

When building the footer, the same line of text will have left-justified, centered and right-justified text (try doing this in MS Word...).

To build the footer, create "Footer A":
In summary:

Detailed Footer Steps:

a. Place the cursor at the top of the document.  Alternately, place the cursor on the first page where you want the header-and-footers to begin, usually after the cover page.  Changes to Headers and Footers take effect on the current page and flow down, through the remainder of the document.  However, as you will see below, you can control when the header/footer "activates."  I prefer to set all document-level codes at the top of the first page, where they are easier to find.

b. Select menu Insert, Header/Footer.

c. Choose "Footer A", then "Create"

(Because of Draft View, a new document window opens, which obscures the original document. Do not panic -- your document/paper is in the background.)

d. Press Enter (Return) one time. (I like a blank line, separating the footer from the text above)

e. Optionally, insert a Horizontal Line by choosing menu: Insert, Line, "Horizontal Line". Note Reveal Codes shows a [Graph Line].

f. Press Enter (Return) after the graphic-line.
g. Type Your name (e.g. J.Smith); this aligns along the left margin.

h. Insert the current Page Number:
  • Press "Shift-F7" (Center)
  • Select Menu: Format, Page, "Insert Page Number"
  • Choose "Page"
  • Click "Insert"

Notice how you can center the page-number-text on the same line, even though other text was already typed. Your name stays along the left margin while the new text centers. Once again, this shows how WordPerfect is different than MSWord. Most text controls (Centering) take effect at the cursor position -- they do not apply to the entire line or paragraph. This allows you to make complicated footers using only a few keystrokes.

Alternately, after centering, you could type the word "Page " (space), then "Insert the Page Number," giving a "Page #".

i. Next, type the Class-name with these formatting steps:

Immediately after the [Pg Num Disp] reveal-code (at your current cursor position), press Alt-F7 (Flush Right) and type "Biology 202".

The screen, with Reveal Codes, will look like this:

Click image for a larger view, click right-x to return

j. Click the Document's "X" (near the upper-right, illustrated above); this returns you to the original document.

Because you are in Draft Mode, the footers are not visible while typing in the main paper.  They can be seen in Print Preview or by switching to Full View mode (not recommended).

Headers with the Paper's Name

Similarly, define a Header at the the top of each page in the paper. For example, here is the paper's name, justified to the right:

a. Confirm the cursor is near the top of the document (the exact position in Reveal Codes is not particularly important. If you have made no other editing changes, the Header will appear next to the previously-built Footer, as seen in Reveal Codes; this is acceptable.)

b. Select top menu:
  • Insert, Header/Footer
  • Choose Header A
  • Create
    (Opening a new editing window, obscuring the original document).
  • Press Alt-F7 (Flush, Right)
  • Type your Paper's name; e.g. The Secret Lives of Turtles
  • Press Enter one time (I like a blank line below the header)

Click the document's "X" to close this window, returning you to the (blank) document (note: this does not close WordPerfect).  Because you are in Draft View, the Header is hidden while you type in the main document.

Cover Page

Up to this point, you have a document defined with a default font, margins, tab-settings, and page-number footers (which will be disabled for the first page in a moment). Follow these steps to write the Paper's Cover Page.

a. Disable the Headers and Footer for this page only.
  • Select menu: Format, Page, "Suppress"
  • Check "Header A"
  • Check "Footer A"
  • Click OK

Note: I prefer to define the document's Headers and Footers on the first page of the document and then disable them where not needed. This way, their location is easily remembered and easily found. Alternatively, you could create the Headers and Footers on the first "real" page of the paper and let them "flow down" through the remainder of the document.

b. Press 8 or 9 Returns (Enter), to move the cursor down the page.

c. Press Shift-F7 (Center) and type the document's title "The Secret Lives of Turtles".

d. Highlight the text and change the font by pressing F9.
  • Choose "Extra Large"
  • Click OK

e. Click the white area after the large text
(note how the editing cursor appears after the closing [ExtLarge] Reveal Code).

Continue building the remaining text of the cover page, pressing Shift-F7 to center each line and press Return to move to a new line. Results will look similar to this:

The Secret Lives of Turtles

Your Name
Biology 202
July, 2011

f. Press Ctrl-Enter to insert a Hard Page Break,
moving you to the first "real" page in the paper.

First Page Duplexed

Decision: If the paper is being printed duplex (front-and-back), the first "real" page should begin on an odd-numbered page; not on the back of the cover-page (page-2).

The cheap and sleazy way to accomplish this is to insert a second Hard-Page (ctrl-Enter), moving you to "page-3". Do this now.
  • Press Ctrl-Enter (insert a second Hard Page Break)

Alternately: on the first page-break, Menu: Format, Page, "Force Page: Odd"

First Page of Paper

Because of the cover page(s), the first "real" page in the paper starts on physical page-2 /or 3 -- but it should be numbered as page "1".

Resetting the Page Number:

a. With the editing cursor on the last page, select menu: Format, Page, Numbering.
b. Click "Set Value", type "1"

Change the Page-Number layout on Page 1:

Traditionally, page 1 nomenclature should be at the bottom, center of the page, with no other accouterments -- in other words, no fancy footer. Once again, go into the Suppress menu and make this minor change:

a. Menu: Format, Page, Suppress
b. Click only on "Footer B"
c. Check [x] Print Page Number at Bottom Center

Begin Typing your Paper!

This completes the Paper's setup. You can now begin typing the paper. You will find all page numbers behave appropriately, and automatically with professional-looking formatting. After typing a few pages, switch to View, "Page" to see the results -- but I recommend switching back to "Draft" to keep editing.

Related Articles:
Hanging Indents (out-dents)
"Block-Protecting" text - automatically forcing a page-break if an illustration and related text crosses a page-boundary.
WordPerfect X5 - A Quick Review

Future WordPerfect Articles:

Using Tabbed-Tables
Insert Graphics and Illustrations

Your comments are welcome.

Friday, July 1, 2011

Blogspot Blogger Edit Pencil Missing

Problem: Blogger Blogspot Edit-Pencil Missing. Last updated 2011.07.24. Solution likely found.

When using Blogspot's editor, I noticed the Pencil icon was missing and I could no longer enter edit-mode from the View Blog screens. Also, the tool-box icon is missing from the widget edit screens.


I have found a solution to the problem but am unconvinced this is the best solution. One thing is for sure, this is more-than-likely a browser-related problem -- especially if the feature worked in the past but doesn't any longer. Use the following diagnostics to help identify the problem.

Update 2011.08.16: I returned these suggested changes back to default and discovered the Pencil-icon had returned. Also, during this intervening time, Firefox was upgraded from version 4 to 5, then to 6. Perhaps, also, Google changed how the Pencil icon was displayed. In any case, here is what I had done.

Confirm the Feature is Enabled:

Confirm the edit feature is enabled on your blog with these steps:

A. Sign-in to the Blog, go to DashBoard, click "Settings"

B. In the [Basic] tab, confirm "Show Quick Editing" = Yes

C. In the [Design] tab, (page-elements), look in the "Blog Posts" widget, likely on the left. Click "Edit"

D. In the popup window, confirm [x] "Show Quick Editing" is checked.


Prove if your Blogger Template supports the edit-icon; this also may prove if this is a browser-related issue.

Comments: For most Blogspot users, I doubt the template is the real problem and Template editing is not necessary.

If you are using a Blogger-supplied template, the edit feature will be there. In my case, I spent a fruitless hour backing up and editing the template, trying to figure this out, when I finally decided to test in IE -- proving the template was good and the problem was related to Firefox version 4 or 5. Later, I found the feature worked in Firefox 5 on a different machine, indicating this is really a problem with this particular installation.

If the pencil-edit icon *used to work*, but doesn't any longer, it is likely because of a browser or browser update. Do the following to test:

a. Sign-in to your Blogger account with Internet Explorer (7 or 8 -- there have been reported issues with version 9 - not substantiated).

b. Create a new test Blog Entry -- which forces Blogspot into Edit mode.

(Important: Blogger does not always show the edit-pencil until after something is edited one-time. For each test, edit something one time (using the manual steps described above).

Save the post.

c. Click "View Blog". Look for the edit-pencil.

d. Decision:

If the edit-pencil shows up in IE, but not in other browsers, especially FireFox, this proves the problem is with the browser and not the template. I have not tested Chrome or other browsers.

If the edit-pencil does not show up in any browser, see this Linked: article and text below, which suggests that some Blogger templates may not have this feature installed. This is a technical article. Be sure to backup your templates before making changes. It may be easier to just try a different template to see if the problem persists.

Possible FireFox Solution #1:

This solution has fixed the problem on my machine and it requires changing an internal FireFox setting. Change this setting: "network.cookie.thirdparty.sessionOnly" Change from the default false to true.

a. On the FF URL line, type this address: "about:config" (no quotes)
b. Click "I promise to behave" when prompted about the damage you can cause
c. In the top-center search box, type:

"network.cookie.thirdparty.sessionOnly" (no quotes. Similar screen illustration, below)

d. Change the value from false to true.
e. Close Firefox and reload the browser/blogger to see the results. Remember, you must edit something at least one time before you will see the results.

Possible FireFox Solution 2:

Note: This solution is not necessarily recommended.
In a previous test, I used Firefoxe's Config screens to block Sending Referer Headers. This seemingly unrelated setting has something to do with the edit-pencil because it temporarily fixed the problem, but later it again failed. Because of this, I have since returned this setting back to the default value of "2" and no longer recommend this solution, but this is widely discussed on the web and it must have worked for some people.

a. On the FF URL line, type this address: "about:config" (no quotes)
b. Click "I promise to behave" when prompted about the damage you can cause
c. In the top-center search box, type: "network.http.sendRefererHeader" (no quotes)
d. Change the value from (2) to zero.
e. Close Firefox and reload the browser/blogger to see the results.

The Referer is often set to =2 as a minor security measure; it stops sites from tracking where you came from. It is safe to change this value.

If you do not find the key, this solution will not help you. Older versions of FF did not have a default value for SendRefererHeader, but the newer versions do. (The new default sets the value = 2).

This option is not available in IE and IE will not have this problem.

Other Possible Solution (untested by keyliner):

Others have reported this:
"My icons recently went missing, even though the Quick Edit setting was turned on. I went into the settings, turned it off, pressed Save Settings, turned it back on and pressed Save Settings again and they re-appeared."

Another blog reported this:
Go to the Privacy tab in your Internet Options. Click the Sites button.
Type "" in the Address of website field and then press the Allow button.
Press OK twice to save the settings.

Reload your blog

In Closing:

On two machines, both running Firefox 5 and both with ostensibly the same settings, the edit-icon worked on one but not the other. Changing network.cookie.thirdparty.sessionOnly to false inexplicably resolved the problem on the broken machine.

Searching for other solutions to this problem, you will find discussions on Java-code problems. I suspect for most of us, this is not the solution. This can be simply tested by launching the editor in IE -- proving the Java-code is probably correct.

The suggestions on Java code center around this statement:

b:include data="post" name="postQuickEdit"

Moving this code to other locations, as suggested in the Link's reader comments, did not help. Plus, mere mortals should not have to work this hard for something that used to work, but now does not. All of this suggests the template is likely correct and the problem lies elsewhere.

Other Things Tested:

Changing to a new BlogSpot template did not help.

Additionally, using a suggested Blogger's DESIGN, Edit HTML, "Revert Widget Templates to Default" did not help.

Flushing Firefox's Cache does not help.

Switching to BlogSpot's new Editor or Old Editor made no difference.

And, even with all of this testing, each of these solutions does not make sense because the editor works in IE.

In the end, at least on my machine, changing the 3rd party cookie setting on one machine (but not another) fixed the problem. This is not a sensible solution.

Your unregistered comments are welcome.