Microsoft Office Tutorials and References
In Depth Information
If you've worked with programming languages, you may be familiar with the concept of
regular expressions. A regular expression is a way to match strings of text using very
concise (and often confusing) codes. Excel does not support regular expressions, but if
you search the Web, you'll find ways to incorporate regular expressions in VBA, plus a
few add-ins that provide this feature in the workbook environment.
Try PUP for data cleaning
My Power Utility Pak add-in consists of more than 50 general-purpose Excel utilities — including several tools that
can assist you when cleaning up data. The one I use most often is called Text Tools.
Text Tools has options to
• Change the case of text: uppercase, lowercase, proper case, sentence case, and toggle case.
• Add text to cells: Specify text to add before the first character, after the last character, or after a specific charac-
• Remove by position: Specify the number of characters to remove and the location within the cell.
• Remove spaces: Leading spaces, trailing spaces, excess spaces, or all spaces.
• Delete characters: Nonprinting, alpha, non-alpha, numeric, non-numeric.
Text Tools is a stay-on top dialog box, so it's always handy. It operates directly on the text, so no formulas are re-
quired. It's fast and even has an Undo feature.
You can download a free 30-day trial from my website ( spreadsheetpage.com ) . Or, use the coupon in the
back of the book to purchase a discounted license.
Adding text to cells
If you need to add text to a cell, the only solution is to use a new column of formulas. Here are some examples.
This formula adds: “ID: ” to the beginning of a cell:
This formula adds “.mp3” to the end of a cell: