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.
Search JabSto ::




Custom Search