Microsoft Office Tutorials and References
In Depth Information
The TEXT function converts text from one specific number format to another. You can also
access the formatting of text by choosing Format, Cells, and select the Numbers tab. Under
Categor y, choose Custom. From there you add custom formats.
For example, let’s say you want to create a custom format that includes the word “Ton” after
all the numbers. In a cell where the number is 75, select the General type and include “Ton”
in quotes after General. Your result would be 75 Ton. This also allows you to calculate
numbers from cells even though Ton shows up in the cell.
This is a numeric value, reference to a cell, or a formula that
calculates to a numeric value.
This is a number format in text. Also see the custom number formats
from format cells as mentioned above.
=TEXT(“3/16/64”,”mmmm dd, yyyy”) would result in March 16, 1964.
=TEXT(.3,”$0.00”) would result in $0.30.
You can also use cell referencing for the value.
TRI Use the TRIM function to remove all spaces from text except for single spaces between words.
The TRIM function is another cleaning function that removes spacing between words. For
example, if you inherit a list of information and there happens to be random spacing between the text
at different locations, you can trim away the spacing by referencing the cell. For example, you’ll
notice in Figure 12.12 that the random spacing in the left column shows multiple mixtures in
spacing. However, by applying the TRIM function to the right, you can eliminate the spacing. Use
the Paste Special command to paste the cleaned text back into the original column as values.
This function is specifically useful when data is imported from other applications, particularly
mainframe and DOS applications, where spacing often is used as separators in lists and forms.
This is the text or cell reference containing text you want to remove all the