Finding a particular character in a text string –
Excel’s LEFT, RIGHT, and MID functions work great for extracting text, but only if you
know the exact position of the characters you are targeting. What can you do when you
don’t know exactly where to start the extraction? For example, if you had the following list
of product codes, how would you go about extracting all of the text after the hyphens?
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 below, 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)
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")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)