Microsoft Office Tutorials and References
In Depth Information
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
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).
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.
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:
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.