Microsoft Office Tutorials and References
In Depth Information
0000097239 ) to the cell, the leading zeros will be deleted. You can ensure Excel will treat your
input as a string by changing the cell’s number format to Te xt . You would perform this action
in the Excel interface by clicking Format, Cells, displaying the Number tab page, and clicking
Text. You can do the same thing using the Range object’s NumberFormat property.
For example, if you wanted to change the number format of the active cell (which is consid
ered a range in this context) to Te xt , you would use the following line of code:
ActiveCell.NumberFormat = "@"
You can change the number format of a range to any of the values displayed in the list of
Custom number formats available in the Format Cells dialog box (shown in Figure 9-1). If
you’re not sure which format to use, assign the format to a cell using the Format Cells dialog
box, and then click Custom to display the code. Be sure to enclose the code in quotes!
Figure 9-1. You can change the format of your cell to any of the formats in the Custom list.
Preparing String Data for Processing
Once you’ve determined that the data you’re about to perform text operations on is, in fact,
text, you can take additional steps to ensure that the data will look its best when you process
it. There are two functions you can use to process your data: CLEAN and TRIM. The CLEAN
function strips out any nonprinting characters from a string. Nonprinting characters are also
known as control characters , because they’re usually entered by pressing the Ctrl key while
typing another key sequence. Nonprinting characters don’t often show up in text files or
worksheets, but if you import data from another program into Excel you might find them
sneaking in as interpretations of formatting or data structure instructions that weren’t
stripped out when the original data was saved.