Chapter 25: Importing and Cleaning Data
2525
You can use a formula like this to replace those spaces with normal spaces:
=SUBSTITUTE(A2,CHAR(160)," ")Or you can use this formula to replace the nonbreaking space character with normal spaces
and to remove excess spaces:=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Removing strange characters
Often, data imported into an Excel worksheet contains strange (sometimes unprintable)
characters. You can use the CLEAN function to remove 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. It’s programmed to remove the first 32 non-
printing characters in the 7-bit ASCII code. Consult the Excel Help system for information on how to remove the non-
printing Unicode characters. (Search Help for the CLEAN function.)
Converting values
In some cases, you may need to convert values from one system to another. For example,
you may import a file that has values in fluid ounces, and they need to be expressed in mil-
liliters. 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 in
the following categories: weight and mass, distance, time, pressure, force, energy, power,
magnetism, temperature, volume, liquid, area, bits and bytes, and speed.Excel can also convert between number bases. You may import a file that contains hexa-
decimal values, and you need to convert them to decimal. Use the HEX2DEC function to
perform this conversion. For example, the following formula returns 1,279, 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.Excel 2013 introduced a new function, BASE, that converts a decimal number to any num-
ber base. Note that there is not a function that works in the opposite direction. Excel does