This post looks at using a couple of text functions together. The MID and FIND functions are used to extract text from a cell.
The example below shows a list of five films, each with the year they came out. We want to extract the year from the text and enter it into a different cell. We can then use the year in filters, sorts and pivotTables.
The MID function is used to extract a specified number of characters from a text string. However it needs a starting character and because the starting position varies between each cell it will need some help.
The FIND function is used to return the starting position of a character. Because the year is enclosed in brackets, this can be used to find the opening bracket of each cell and it gives the MID function its starting position.
The formula entered in cell B2 is shown below.
The +1 is used so that the MID function starts from the character after the opening bracket.
Check out the video below for a demonstration of the formula above.