Microsoft Office Tutorials and References
In Depth Information
Understanding text functions
The TRUNC function
The TRUNC function truncates everything to the right of the decimal point in a number,
regardless of its sign. It takes the arguments ( number, num_digits ). If num_digits isn’t
specified, it’s set to 0. Otherwise, TRUNC truncates everything after the specified number of
digits to the right of the decimal point. For example, the formula =TRUNC(13.978) returns the
value 13 ; the formula =TRUNC(13.978, 1) returns the value 13.9 .
Understanding text functions
Text functions in Excel are some of the most useful word-processing and data-management
tools you’ll find anywhere—they perform tasks that word-processing programs can’t. These
functions are conveniently listed for you when you click the Text button on the Formulas
tab on the ribbon.
You can use the TRIM and CLEAN functions to remove extra spaces and nonprinting
characters, which is great for cleaning up imported data—a task that ranges from difficult to
impossible using search and replace. The UPPER, LOWER, and PROPER functions change the
case of words, sentences, and paragraphs with no retyping. You might find yourself copying
text from other documents into Excel just so that you can apply these functions. After using
text functions, select the cells containing the formulas, press Ctrl+C to copy, click the Paste
button on the Home tab, and then click Paste Values to convert the formulas to their
resulting (text) values. You can then copy the edited text into the original document.
In the following sections, we discuss the most useful Excel text functions.
Using selected text functions
Text functions convert numeric entries, as well as numeric text entries, into text strings so
that you can manipulate the text strings themselves. Numeric text is a type of numeric
entry that provides a few specific text characters in addition to numeric characters. For
details, see “Using numeric text in formulas” in Chapter 12.
The TEXT function
The TEXT function converts a number into a text string with a specified format. Its
arguments are ( value, format_text ), where value represents any number, formula, or cell
reference; and format_text is the format for displaying the resulting string. For example, the
formula =TEXT(98/4, "0.00") returns the text string 24.50 . You can use any Excel formatting
symbol ($, #, 0, and so on) except the asterisk (*) to specify the format you want, but you
can’t use the General format.
Search JabSto ::




Custom Search