Microsoft Office Tutorials and References

In Depth Information

**Using LEN to Find the Number of Characters in a Text Cell**

•
Ask MID to start at the fifth character, but calculate the exact

number of characters by using the LEN function (that is,

=MID(A2,5,LEN(A2)-4)).

•
Skip MID altogether and ask RIGHT to return all the characters

after the first dash. This requires you to use the FIND function to

locate the first dash
—
that is, =RIGHT(A2,LEN(A2)-FIND("-",A2)).

Using

Using
LEN

to Find the Number of Characters in a Text Cell

It seems pretty obscure, but you will find the LEN function amazingly use-

ful. The LEN function determines the length of characters in a cell, including

any leading or trailing spaces.

LEN
to Find the Number of Characters in a Text Cell

Syntax:

=LEN(text)

The LEN function returns the number of characters in a text string. The ar-

gument textis the text whose length you want to find. Spaces count as char-

acters.

There are instances in which you can use LEN along with LEFT, MID, or RIGHT

to isolate a portion of text.

Toreviewinformationonthistopic,refertotheexampleinthe

previous section.

You can also use LEN to find records that are longer than a certain limit.

Suppose you are about to order nameplates for company employees. Each

nameplate can accommodate 15 characters. In
Figure 11.59
,
you add the LEN

function next to the names and sort by the length, in descending order. Any

problem names appear at the top of the list.