Microsoft Office Tutorials and References

In Depth Information

=TRIM(SUBSTITUTE(A2,CHAR(160),” “))

Removing strange characters

Often, data imported into an Excel worksheet contains strange (often unprintable) characters. You can use the

CLEAN function to all nonprinting characters from a string. If the data is in cell A2, this formula will do the

job:

=CLEAN(A2)

The CLEAN function can miss some nonprinting Unicode characters. This function is

programmed to remove the first 32 nonprinting characters in the 7-bit ASCII code. Con-

sult the Excel Help system for information on how to remove the non-printing Unicode

characters (search Help for the CLEAN function).

Converting values

You may need to convert values from one system to another. For example, you may import a file that has values

in fluid ounces, but those values need expressed in milliliters. Excel's handy CONVERT function can perform

that and many other conversions.

If cell A2 contains a value in ounces, the following formula converts it to milliliters.

=CONVERT(A2,”oz”,”ml”)

This function is extremely versatile and can handle most common measurement units.

See Chapter 10 for more information about the CONVERT function.

Excel can also convert between number bases. You may import a file that contains hexadecimal values, and you

need to convert them to decimal. Use the HEX2DEC function to perform this conversion. For example, the fol-

lowing formula returns
1,279,
which is the decimal equivalent of its hex argument:

=HEX2DEC(“4FF”)

Excel can also convert from binary to decimal (BIN2DEC) and from octal to decimal (OCT2DEC).

Functions that convert from decimal to another number base are DEC2HEX, DEC2BIN, and DEC2OCT.