Chapter 11: Using Formulas to Manipulate Text
11
You can use the FIND function as an argument in a MID function to extract a set number
of characters after the position number returned by the FIND function.
Entering this formula in a cell will give you the two numbers after the first hyphen found in
the text. Note the +1 in the formula. This ensures that you move over one character to get to
the text after the hyphen.
=MID("PWR-16-Small",FIND("-","PWR-16-Small")+1, 2)Finding the second instance of a character
By default, the FIND function returns the position number of the first instance of the
character for which you are searching. If you want the position number of the second
instance, you can use the optional Start_Num argument. This argument lets you specify
the character position in the text string to start the search.
For example, this formula will return the position number of the second hyphen because
we’re telling the FIND function to start searching at position 5 (after the first hyphen):
=FIND("-","PWR-16-Small",5)To do this dynamically (without knowing where to start the search), you can nest a FIND
function as the Start_Num argument in another FIND function. You can enter this for-
mula into Excel to get the position number of the second hyphen:
=FIND("-","PWR-16-Small",FIND("-","PWR-16-Small")+1)Figure 11.7 demonstrates a real-world example of this concept. Here we are extracting the
size attribute from the product code by finding the second instance of the hyphen and
using that position number as the starting point in the MID function. The formula shown
in cell C3 is as follows:
=MID(B3,FIND("-",B3,FIND("-",B3)+1)+1,10000)FIGURE 11.7
Nesting the FIND function to extract everything after the second hyphen
This formula tells Excel to find the position number of the second hyphen, move over one
character, and then extract the next 10,000 characters. Of course, there aren’t 10,000 char-
acters, but this ensures that everything after the second hyphen is pulled.