Excel - Sakai Grade Book Point Extraction - Part II of IV: Episode 1542
1,189 views · Published 11 April 2012 · 4:04 · Indexed 21 October 2026
Channel: MrExcel.com · 2012 · Education
Microsoft Excel Tutorial: Extracting Points Possible from Sakai Grade Book | Using Formulas to Extract Points. Welcome back to another MrExcel podcast! In this episode, we will continue our discussion on how to extract points from the Sakai grade book. In the previous episode, we talked about using a macro to do this, but today we will explore using formulas instead. If your grade book is not exactly like the Sakai grade book, or if you prefer not to use a macro, then this method is for you. We will be focusing on extracting the points possible from the headings, which are denoted by square brackets. These headings may vary in length and format, but they all have the square bracket in common, which we will use to our advantage. To start, we will use the MID function to extract the points from the headings. We will also use the FIND function to locate the left square bracket within the heading. Then, we will add 1 to go one character past the bracket, and use a large number for the number of characters we want to extract. This will give us the points possible, along with the square bracket. Next, we will use the SUBSTITUTE function to get rid of the square bracket. This function will replace every occurrence of the right square bracket with a blank space. This will leave us with just the points possible, without the square bracket. However, this will still be in text format, so we will need to add a +0 to convert it to a number. Once we have this formula, we can copy it across all the columns to extract the points for each student. We will then insert four columns and use the AutoSum function to get the total points possible for each student. It is important to note that if you are doing this for the students, you should not use the AutoSum function as it may stop when it encounters a blank cell. Instead, you should manually select all the cells to be included in the sum. In the next episode, we will take this data and calculate the percentage for each student. This formula may be a bit more complicated, but we will walk you through it step by step. Thank you for tuning in to this MrExcel podcast, and we will see you next time for another informative netcast. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Extracting Points Possible from Sakai Grade Book (00:10) Using Formulas to Extract Points Possible (01:00) Using the SUBSTITUTE Function to Remove Square Brackets (02:07) Converting Text to Numbers (02:38) Inserting Columns and Using AutoSum (03:11) Wrinkle in Tomorrow's Formula (03:44) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: AutoSum in Excel Converting text to numbers Episode 152 Excel formulas Extract points possible FIND function Learn Excel from MrExcel podcast Macro for grade book data MID function MrExcel.com Sakai grade book SUBSTITUTE function Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153352/ An alternate method to Extract the Heading Values of your Sakai Grade Book - a method that does not use VBA Macros. Follow along with Bill Jelen as he provides a Formula solution to extracting those bracketed Values in Episode #1542. 'Livening' up your Grade Book. This is Part II of IV. Formulas for the 'Grade Book Application' that Bill Jelen has created are from the book, "Excel For Teachers" [ http://www.mrexcel.com/teachers.html ] published by Holy Macro! Books. Learn more about the book "Excel For Teachers" by clicking here [ http://www.mrexcel.com/teachers.html ] to go to the "Excel For Teachers" information page. ...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE2010.html "The Learn Excel from MrExcel Podcast Series" Visit us: MrExcel.com for all of your Microsoft Excel Needs!
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920