Excel 2019 Bible

Part II: Working with Formulas and Functions

FIGURE 11.4

Removing excess spaces from text

The TRIM function is relatively straightforward. Simply give it some text and it will remove

all spaces from the text except for single spaces between words.

As with other functions, you can nest the TRIM function in other functions to clean up

your text while applying some other manipulation. For instance, this function trims the

text in cell A1 and converts it to uppercase all in one step:

=UPPER(TRIM(A1))

It’s important to note that the TRIM function was designed to trim only the ASCII space

character from text. The ASCII space character has a code value of 32. In the Unicode char-

acter set, however, there is an additional space character called the nonbreaking space char-

acter. This character is commonly used in web pages and has the Unicode value of 160.

The TRIM function is designed to handle only CHAR(32) space characters. It cannot, by

itself, handle CHAR(160) space characters. To handle this kind of space, you’ll need to uti-

lize the SUBSTITUTE function to find CHAR(160) space characters and replace them with

CHAR(32) space characters so that the TRIM function can fix them. You can accomplish

this all at one time in the following formula:

=TRIM(SUBSTITUTE(A4,CHAR(160),CHAR(32)))

For a detailed look at the SUBSTITUTE function, see the section “Substituting text

strings” in this chapter.

Extracting parts of a text string

One of the most important techniques for manipulating text in Excel is the ability to

extract specific portions of text. Using Excel’s LEFT, RIGHT, and MID functions, you can do

things like this:

■ (^) Convert nine-digit postal codes into five-digit postal codes
■ (^) Extract phone numbers without the area code
■ (^) Extract parts of employee or job codes for use somewhere else
Figure 11.5 demonstrates how using the LEFT, RIGHT, and MID functions can help easily
accomplish these tasks.