Microsoft Office Tutorials and References
In Depth Information
SUBSTITUTE(): Replacing One Sequence of Characters with Another
Manipulating Text
Gem in the roUGh
Using PROPER() to change Names in All Caps to Initial Caps
Usually, functions like UPPER(), LOWER(), and PROPER()
transform the appearance of text that’s already in your
spreadsheet. But you could have a tricky time figuring out
exactly how to do this transformation.
Once you’ve taken this step, Excel displays the properly
capitalized names in column B. But you can’t just copy and
paste these cells into column A (because they hold
formulas that reference the cells you’d be pasting them into).
Instead, you need to select the corrected names from column
B, copy them (Home➝Clipboard➝Copy, move to cell A1,
and then select Home➝Clipboard➝Paste➝Values (not
the plain-vanilla Home➝Clipboard➝Paste command).
Say you’ve got a list of first and last names in column A
(beginning in cell A1) whose letters are all uppercase. You
want to change these names so that only the first letter
of each name’s capitalized. Here’s what to do. First, insert
a new column B. Next, enter the formula =PROPER(A1)
in cell B1, and then copy this formula to the rest of the
cells in column B. Because this formula uses a relative cell
reference (as explained on page 487), Excel automatically
adjusts the formula for each cell that you paste it into.
This technique provides a quick way to clean up a number
of problems. For example, you can also use this approach
with many other text functions like TRIM(), CLEAN(),
SUBSTITUTE(), TEXT(), FIXED(), DOLLAR(), all of which are
explained in this chapter.
CLEAN() simply removes non-printable characters from a text string. Non-printable
characters, which usually appear as empty-box icons in your text, tend to appear
only if you import some text from another file format that Excel has difficulty
understanding.
SUBSTITUTE(): Replacing One Sequence of Characters with Another
The SUBSTITUTE() function replaces a sequence of characters in a string with
another set of characters. The function has three parts: the text you want to modify,
the characters you’re looking to replace, and the replacement text you want to insert.
In addition, you can supply an optional occurrence number parameter, which Excel
uses if it finds more than one match. For example, if your search text’s matched three
times, and you supply 2 for your occurrence number, Excel changes only the second
occurrence. If you don’t supply the occurrence number, Excel changes all
occurrences. Here’s what the function looks like:
SUBSTITUTE(text, old_text, new_text, [occurrence_number])
Consider the case where cell A1 contains the text: It was the best of times; it was the
worst of times . You could use the following formula:
=SUBSTITUTE(A1, "times", "nanoseconds")
The result is the string: It was the best of nanoseconds; it was the worst of nanoseconds .
On the other hand, the following formula explicitly replaces just the second
occurrence. The resulting string is: It was the best of times; it was the worst of crimes .
=SUBSTITUTE(A1, "times", "crimes", 2)
Search JabSto ::




Custom Search