How to extract breed, tag, flock no and year drop from Sheep Genetics ID using Excel

How do I extract breed, tag, flock no and year drop from Sheep Genetics ID using Excel?

If you have an excel file that contains your 16 digit Sheep genetics ID you can extract various types of information from this file. 

Step 1

• Make four columns next to the SG ID column with the headings: Breed, Flock, Year, Tag.

Step 2

• In the first box of the Breed column type in the following formula  =LEFT(A2,2)  and press Enter.

• A2  is selected as it is the first SG ID in the list. You will select this number by clicking on that box whilst writing the formula.
• LEFT instructs the formula to use numbers from the left hand side of the SG ID
• 2 tells it to collect only 2 numbers.

• You will need to drag this formula down, so it can be applied to all cells in that column. Consequently in the breed column will be the 2 numbers associated with the breed of that animal, in this case 50 for Merino.

Step 3

• Underneath the Flock heading you will need to type in the formula  =MID(A2,3,4)

• A2 is the same as above.
• MID instructs the formula to use numbers from the middle of the SG ID.
• 3 instructs the formula to use numbers 3 spots in from the start of the number.
• 4 instructs the formula to collect four numbers.

• You will need to drag this formula down so it can be applied to all cells in that column. Consequently in the flock column will be the 4 numbers associated with the flock of that animal in this case 1234.

Step 4

• Underneath the Year heading you will need to type in the formula  =MID(A2,7,4)

• A2  is the same as above.
• MID  instructs the formula to use numbers from the middle of the SG ID.
• 7  instructs the formula to use numbers 7 spots in from the start of the number.
• 4  instructs the formula to collect four numbers.

• You will need to drag this formula down so it can be applied to all cells in that column. Consequently in the year column will be the 4 numbers associated with the birth year of that animal in this case 2011.

Step 5

• Underneath the Tag heading you will need to type in the formula  =RIGHT(A2,6)

• A2  is the same as above.
• RIGHT  instructs the formula to use numbers from the right hand side of the SG ID.
• 6  instructs the formula to collect four numbers.

• You will need to drag this formula down so it can be applied to all cells in that column. Consequently in the Tag column will be the last 6 numbers of the SG ID associated with the tag of that animal.

Step 6

• You will then end up with a file similar to that shown below.