Finding a Particular Character in an Excel 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 do 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 the text after the hyphen?
The LEFT function wouldn’t work because you need the right few characters. The RIGHT function alone won’t work because you need to tell it exactly how many characters to extract from the right of the text string. Any number you give will pull either too many or too few characters from the text.
The MID function alone won’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 often will need to the find specific characters in order to get the appropriate starting position for 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, you 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, you find the position of the hyphen and use that position number to feed the MID function.
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 returns 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 returns the position number of the first encounter.
For instance, the following formula searches 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.
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. Including +1 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)