Part II: Working with Formulas and Functions
PRT-432
COPR-6758
SVCC A LL-58574
The LEFT function wouldn’t work because you need the right few characters. The RIGHT
function alone wouldn’t work because you need to tell it exactly how many characters to
extract from the right of the text string. Any number that you give will pull either too
many or too few characters from the text. The MID function alone wouldn’t work because
you need to tell it exactly where in the text to start extracting. Again, any number you
give will pull either too many or too few characters from the text.
The reality is that you will often need to find specific characters to get the appropriate
starting position for the extraction. This is where Excel’s FIND function comes in handy.
With the FIND function, you can get the position number of a particular character and use
that character position in other operations.
In the example shown in Figure 11.6, we use the FIND function in conjunction with the
MID function to extract the middle numbers from a list of product codes. As you can
see from the formula, we find the position of the hyphen and use that to feed the MID
function.
=MID(B3,FIND("-",B3)+1,2)
FIGURE 11.6
Using the FIND function to extract data based on the position of the hyphen
The FIND function has two required arguments. The first argument is the text you want to
find. The second argument is the text you want to search. By default, the FIND function
will return the position number of the character you are trying to find. If the text you are
searching contains more than one of your search characters, the FIND function will return
the position number of the first encounters.
For instance, the following formula will search for a hyphen in the text string PWR-16-
Small. The result will be a number 4 because the first hyphen it encounters is the fourth
character in the text string.
=FIND("-","PWR-16-Small")
