office-winner

Removing excess spaces and non printing characters Often data imported into an Excel worksheet contains excess spaces or strange (often unprintable) characters. Excel provides you with two functions to help whip your data into shape: TRIM and CLEAN:  TRIM removes all leading and trailing spaces, and it replaces internal strings of multiple spaces by a single space.  CLEAN removes all nonprinting characters from a string. These “garbage” characters often appear when you import certain types of data. This example uses the TRIM function. The formula returns Fourth Quarter Earnings (with no excess spaces): =TRIM(“   Fourth    Quarter     Earnings     “)

Displaying formatted currency values as text Excel’s DOLLAR function converts a number to text using the currency format. It takes two arguments: the number to convert, and the number of decimal places to display. The DOLLAR function uses the regional currency symbol (for example, a $). You can sometimes use the DOLLAR function in place of the TEXT function. The TEXT function, however, is much more flexible because it doesn’t limit you to a specific number format. The second argument for the DOLLAR function specifies the number of decimal places. The following formula returns Total: $1,287.37. formula       =”Total: “ & DOLLAR(1287.367, 2)

Joining two or more cells Excel uses an ampersand (&) as its concatenation operator. Concatenation is simply a fancy term that describes what happens when you join the contents of two or more cells. For example, if cell A1 contains the text Tucson and cell A2 contains the text Arizona, the following formula then returns TucsonArizona: =A1&A2 Notice that the two strings are joined together without an intervening space. To add a space between the two entries (to get Tucson Arizona), use a formula like this one: =A1&” “&A2 Or, even better, use a comma and a space to produce Tucson, Arizona: =A1&”, “&A2 Another option is to eliminate the quote characters and use the CHAR function, with an appropriate argument. Note this example of using the CHAR function to represent a comma (44) and a space (32): =A1&CHAR(44)&CHAR(32)&A2 If you’d like to force a line break between strings, concatenate the strings by using CHAR(10), which inserts a line break character....