Microsoft Office Tutorials and References
In Depth Information
Using Other Functions
As you can see in this garden club member
database, the month a member was born appears
in column H, whereas the birthday appears in
column I. Having the birth month and birthday
separated into two columns enables you to sort
the database as needed. For example, it’s easy to
produce a list of everyone with April birthdays,
by either sorting or filtering the Birth Month
column. Let’s suppose though, that you want to
import this list into an e-mail program so you
can send out meeting reminders, garden events,
and the like. Since you also want to congratulate
members on their birthday, you want that
information as well. The only problem is, your e-mail
program does not have separate fields for Birth
Month and Birth Day. Instead, it has one field
called Birthday. Using the CONCATENATE
function, this problem is easily taken care of.
LEFT and RIGHT
The LEFT function is used to extract a certain
number of characters from a text string, starting
at the left. The RIGHT function works similarly,
except it extracts characters starting from the
right. Let’s take a look at one reason why you
might need to do this extracting business. Suppose
you’re in charge of inventory at a large computer
store that caters to corporate customers. You’re
moving to a new computerized inventory system,
and you need to generate product numbers that
take advantage of its capabilities to sort and filter.
You’ve decided to change the inventory numbers
slightly, by adding a store code (extracted from the
current store name) in front of the last portion
of the current product number.
The syntax for the LEFT function is
=LEFT(TextCell,NumberOfCharacters)
The syntax is
=CONCATENATE(Text,Text2,Text3,...and so on)
The syntax for the RIGHT function is
=RIGHT(TextCell,NumberOfCharacters)
As the argument, you can either type the address
of a cell that contains text (text or numbers
treated as text), or a text string (enclosed in
quotations). To solve the problem here, in cell
K2, type =CONCATENATE(I2, “ ”,J2). The result,
shown in cell K2, is a text string that combines
the member’s birth month and day, with a space
in between: “Sep 20”. Now, since your e-mail
program won’t be able to make much sense
of the formula, you simply copy the result in
column K to column L, using the Paste Value
command so that you paste the result and not
the formula. At that point, it’s safe to remove
columns I–K and keep only column L for
importing into your e-mail program.
Consider the worksheet shown in Figure 3-17.
Assume you want to take the first two letters
of the store name (such as “Ca” for the Carmel
store) and add the last four digits of the current
product number. To add these two strings
together into a new string, you’ll use our friend,
the CONCATENATE function. As you can see, to
create the new inventory number in cell D8, you
need to use three functions: =CONCATENATE
(LEFT(A8,2),RIGHT(B8,4)). The LEFT function
extracts the first two characters from the store
name (in this case, “Ca”), whereas the RIGHT
function extracts the last four digits of the
current model number (in this case, “1245”). Finally,
the CONCATENATE function puts them together
to form a single text string, Ca1245. Copy this
formula down column D, and voila! You have
your new inventory numbers, easy as pie.
 
Search JabSto ::




Custom Search