Microsoft Office Tutorials and References
In Depth Information
Using Excel Functions
Working with Formulas and Functions
Writing Text Functions
The text category typically works with the text in a
cell. The cell could have numbers, but the function
in the category doesn’t handle them as values in
that it doesn’t add values such as 2+3 to equal 5.
Depending on the text function it might add them
together as cells so that a cell with 2 and a cell
with 3 becomes 23, or APPLE and PIE become
APPLE PIE. Other functions reveal only a specified
number of characters such as the 3 characters on
the left side of the cell (YORKSHIRE becomes YOR)
or the right four characters (YORKSHIRE becomes
HIRE). Take a look at a few of the text functions:
RIGHT: Displays a specified number of
characters from the right side of a cell. (There’s
also a LEFT function that provides a
specified number of characters from the left side
of a cell.) As an example, beginning at cell
A2, you have a list of inventory items that
begin with a two-digit vendor code and
then a three-digit part number such as
63174. For your purpose you need only the
part number, which is the three characters
on the right. The syntax is =RIGHT( cell
address, number of characters ), so you
would enter =RIGHT(B2,3), which returns
the value 174 (see Figure 9-21).
CONCATENATE: This function joins
together several text cell strings into a single text
string. For example, if in cell A2 you had
Mary and in cell B2 you had Jones, if you
concatenate them you end up with Mary
Jones. The syntax is =CONCATENATE ( cell
address or value or text1, cell address or
value or text2, cell address or value or text 3 ).
You can add together up to 255 different
text strings, numbers, or cell references.
Let’s look at the above example. If you
simply entered the function as =CONCATE-
NATE(A2,B2) you end up with MaryJones.
You need to enter =CONCATENATE(a2, “
”,B2), like you see in Figure 9-20, which
provides Mary Jones. The second argument was
a space, and all manually entered text must
be enclosed in quotes.
PROPER: The PROPER function capitalizes
the first letter in each word of the cell text.
If you have The dog is gone in cell G5, using
the PROPER command results in The Dog Is
Gone . The syntax is =PROPER( cell address ).
UPPER: Converts cell text to all uppercase
letters. The syntax is =UPPER( cell address ). If
the value of cell C3 is John Smith, entering
UPPER(C3) results in JOHN SMITH. There is
also a LOWER command that uses the same
command to convert cell text to all
Adding several text cells together.