Text Tools The Anatomy of a Utility

In this section, I describe an Excel utility that I developed (and is part of my Power Utility Pak). The Text Tools utility enables the user to manipulate text in a selected range of cells. Specifically, this utility enables the user to do the following:

♦ Change the case of the text (uppercase, lowercase, proper case, sentence case, or toggle case).

♦ Add characters to the text (at the beginning, to the end, or at a specific character position).

♦ Remove characters from the text (from the beginning, from the end, or from a specific position within the string).

♦ Remove spaces from the text (either all spaces or excess spaces).

♦ Delete characters from text (non-printing characters, alphabetic characters, non-numeric characters, non-numeric and non-alphabetic characters, or numeric characters).

Figure 16-1 shows the Text Tools utility in action.

monthly.xls

fTfnT

xl

A

B

c

D

E

F

G

H

1

1

January

5

february

april may june

I

Operation:

Change to:

u

I Change cast

ZJ

3EÎEES9

JÜJ

6

1 ..

——-

-

1 Close

7

july

Skip non-text cells

lowercase

-

6

august

■H

1 1 Apply 1

9

September

_

tOGGLE (

:A5E

1_

_

_

10

october

11

november

12

december

13

► ir

M A

► M \sheetl /

Jj

'1

Figure 16-1: Use the Text Tools utility to change the case of selected text.

Figure 16-1: Use the Text Tools utility to change the case of selected text.

The Text Tools utility is available on the CD-ROM that accompanies this book.This is a standalone version of the tool that is included with the Power Utility Pak. It's an add-in file,and you can install it by choosing the Excel Tools ^ Add-Ins command (click the Browse button and locate the *.xla file). Or,you can simply open it by choosing the Excel File ^ Open command. Either way, you'll be able to view the complete VBA source code. (The VBA project is not protected with a password.)

0 0

Post a comment