APRIL 2020 PCWorld 129function does not work, and that’s with a
non-breaking space character used in
webpages. The decimal value is 160, and
the HTML code is . You can remove
this character using a combination of TRIM,
CLEAN, and SUBSTITUTE.
- UPPER/LOWER/PROPER
Another easy group, these functions convert
text in a cell or range of cells to uppercase,
lowercase, or proper case. Proper case is
first letter in caps and remaining letters in
lowercase. The syntax is simple: function,
cell address.- Enter some mixed-case data in column
A; e.g., cAlifornia, nEW yORK, spanISH.
Enter the following formula in column B,
=UPPER(A4); in column C, =LOWER(A4);
and column D, =PROPER(A4). - Notice that Excel corrects all the
misplaced case errors and converts the data
correctly. Copy the formulas down, and
that’s it for this simple one.
NOTE: In Word, you can use Shift-F3 to
cycle through uppercase, lowercase, and
proper case, but this shortcut key is not
available in Excel. Note that the Excel
function =PROPER is called Sentence case in
Word.
- Enter some mixed-case data in column
- REPT
When Lotus 1-2-3 was the only game in
town, you could enter a backslash followed
by any character and Lotus would repeat that
character throughout a cell. If the cell width
grew larger or smaller, so did the character.
In Excel, this feature is handled by the
function REPT. It’s not quite as efficient
because you must add the character to the
formula, then specify how many times you
want that character repeated. This means if
the cell width is increased, the repeated
character is not, and if the cell width is
decreased, the repeated character bleeds
over into the adjacent cell.
The syntax for this function:
=REPT(“*”,5); =REPT(“—“,10),
=REPT(“+”,12). You can repeat
any character on the keyboard plus
symbols.- I F S TATE ME NT
The IF function (also more commonly called
IF statements) work like this: IF, then, else.
Use TRIM to remove extra or padded spaces from
your data.