Use Excel to Find and Extract

You can use two simple Excel formulas with each other in order to find where a string marking a value begins and where you need to begin to extract that string.

First we use the FIND formula to determine at which character in a cell the searched for term begins at. So if we enter the formula:

=FIND("ABC",A1)

. . . it tells us where in the cell the Bates prefix 'ABC' appears. So in the first cell. A1, the prefix appears at the 27th character. We can then extract the Bates number, assuming it is referred to with a consistent number of characters, by using the MID formula.

=MID(A1,B1,12)

The MID formula searches the cell A1 with the raw text, until it finds the character referenced in B1, and then

pulls the set number of characters for the Bates number - in this case 12.