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