What's Up East Coast Column

Read all about it!

You got the lowdown in the column, now get the breakdown. (A very special THANK YOU to Diane Pullen from WUEC for making this column possible!)

ADVERTISE in the popular WUEC magazine and post your events for free! This full colour booklet has been around for over 5 years and several thousands of hard copies are printed and distributed bi-monthly in the Eastern Cape area.
Contact me TODAY to get your listing up. It costs less than you think!

        27 Jun 2016, 11:36 am

        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!

            Showing all records: