Microsoft Office Tutorials and References

In Depth Information

**Using SEARCH or FIND to Locate Characters in a Particular Cell**

The FIND function makes it easy to find the first instance of a particular

character in a cell. However, if your text values contain two instances of a

character, your task is a bit more difficult. In
Figure 11.62
,
the part numbers

in column A really contain three segments, each separated by a dash:

11. To find the first dash, enter =FIND("-",A2)

=FIND("-",A2) in column B.

22. To find the second dash, use the optional start_numparameter to the

FIND function. The start_numparameter is a character position. You

want the function to start looking after the first instance of a dash.

This can be calculated as the result of the first FIND in column B

plus one. Thus, the formula in cell C2 is =FIND("-",A2,B2+1).

33. After you find the character positions of the dashes, isolate the

various portions of the part number. In column D, for the first part of

the number, enter =LEFT(A2,B2
–
1)

=LEFT(A2,B2
–
1). This basically asks for the left

characters from the part number, stopping at one fewer than the first

dash.

Formulaically isolating data between the first and second

dashes can be done, but it helps to break each number down into small parts.

Figure 11.62.

Figure 11.62.
Formulaically isolating data between the first and second

dashes can be done, but it helps to break each number down into small parts.

44. In column E, for the middle part of the number, enter

=MID(A2,B2+1,C2
–
B2
–
1)

=MID(A2,B2+1,C2
–
B2
–
1). This asks Excel to start at the character

position one after the first dash and then continue for a length that is

one fewer than the first dash subtracted from the second dash.

55. In Column F, for the final part of the number, enter

=RIGHT(A2,LEN(A2)
–
C2)

=RIGHT(A2,LEN(A2)
–
C2). This calculates the total length of the part

number, subtracts the position of the second dash, and returns those

right characters.