Microsoft Office Tutorials and References
In Depth Information
Manipulating Text
Manipulating Text
For example, the result of ROUNDUP(1.1, 0) is 2, even though 1.1 is only slightly
above 1. Similarly, the result of ROUNDDOWN(1.9, 0) is 1, even though 1.9 is almost
2. The only time that ROUNDUP() and ROUNDDOWN() don’t change a number is
if it’s already rounded to the appropriate precision. For example, the result of
ROUNDUP(2, 0) and ROUNDDOWN(2, 0) is the same: 2.
When most people learn about ROUNDUP() and ROUNDDOWN() they often
wonder why anyone would want to use a rounding function that’s less precise than
ol’ reliable ROUND(). The answer, not surprisingly, must do with making more
money. If you’re selling discounted Beanie Babies, say, then you might set the price
at 60 percent of the original list price. However, this formula produces prices like
$8.43423411 that you need to round to the nearest penny. Rather than rounding
down (and giving up your fractional cents), you can use ROUNDUP() to make sure
the price is always rounded up, ensuring that you keep every last penny and even
collect a few extra. Never say Microsoft didn’t try to help you make a buck.
Manipulating Text
You can’t use arithmetic operators like + and – with text. If you try to perform a
calculation by referring to one or more cells containing text, Excel displays a #VALUE
error message. However, there’s one operator you can use: the concatenation
operator (&), which joins together text. For example, imagine you have an individual’s first
name in cell A1, and a last name in cell A2. You could join the values from these two
cells to create a full name with this formula:
=A1 & A2
This approach has one drawback. In all likelihood, the first and last name cells don’t
include any leading or trailing spaces. This trait means when you join them, they’ll
get fused into one word, like JohnSmith. One solution is to explicitly add a space
(between quotation marks) into your formula, like so:
=A1 & " " & A2
The important concept in this example is that you can enter string literals —fixed
pieces of text (including spaces)—as easily as you can enter literal numbers. The only
difference between entering literal text and literal numbers is that you have to place
all text between quotation marks. You can stitch together as many pieces of text
together at a time as you want; there’s no limit. The next group of functions showcases
the many different ways that Excel lets you manipulate text.
CONCATENATE(): Joining Strings of Text Together
The CONCATENATE() function lets you join text in exactly the same way the &
operator does. CONCATENATE() joins all the parameters that you supply into one
long piece of text, in the order you specify them.
Here’s an equivalent way to rewrite the aforementioned name-joining formula by
using CONCATENATE() with two pieces of text:
=CONCATENATE(A1, " ", A2)
Search JabSto ::




Custom Search