Do the Splits (Issue 62) by Tiamari

It’s that time of the year again. Whilst still frantically trying to finalize the last of all the year planning at the College, the November exam results are released! That means hours and hours of sifting through 200 pages of student names and codes to find and capture the exam marks of my 68 students. Or not?

This trimester I managed to get my hands on an electronic copy, so… Excel formulas to the rescue! Here are the formulas I used to split the list of 6562 text lines into columns, which I then linked with lookup formulas to my class list.

Example Text String (On Excel this will all be in ONE CELL with spaces ):

2315016530454 ZONO,NE 08120003/044/ 3 08120033/065/ 3 15070413/042/ 3 16030143/064/ 3

TO EXTRACT THE LEFT PART OF A CELL:

We want to put the ID number in a separate column. A20 is the cell reference of the text string, and 13 is the number of characters to extract.

=LEFT(A20;13)

TO EXTRACT ON THE RIGHT:

We need the code group on the right in a separate column. A20 is the cell reference of the text string, and 15 is the number of characters to extract.

=RIGHT(A20;15)

TO EXTRACT TEXT THAT IS NEITHER ON THE LEFT NOR ON THE RIGHT:

In this case the four exam code groups were already (as a whole) separated from the rest, and we now want to place the second code group into a separate column. In the example there are 5 spaces between the code groups, and a group consists of 15 characters. A20 is the cell reference of the text string, 21 is the start number, and 15 is the number of characters to extract.

=MID(A20;21;15)

TO SEPARATE TEXT THAT DOES NOT HAVE A CONSISTENT LENGTH:

In this case the surname and initials were already in a separate column. We will now isolate the surname. A20 is the cell reference of the text string.

=LEFT(A20;(FIND(",";A20;1)-1))

These “split” formulas can work magic. Why capture data if it is already in digital format!

Happy Clicking!