Excel numbers from Word tables

Compared to the last academic year, we can do more face to face teaching. We just need to be savvy about how it’s done! Social distancing is still in place for Scottish Uni’s and so room occupancy numbers are down. We took the step of aligning the lab experiments from two different MSc classes: that way everyone gets 7 or 8 days in a lab near it’s maximum capacity instead of 3 or 4 days at half capacity. The class assignments are lab based, so we’ve also aligned those too. I’m moved the module assessment from a big 2500-3000 word lab report to several shorted quantitative, calculation based assignments. I do like numbers, and this type of format has some real advantages – but it also comes with a sting in the tail: which I’m trying to code out.

Quantitative assignments are nice because numbers and calculations are objective. This means they are easy to mark, transparent to score and feedback is clear. This is especially true when everyone is doing the same problem, with the same starting numbers, the same calculation and everyone should get the same answer. However in the MSc class this year, the calculations in the individual assignments are based upon student’s own measurements. Therefore, the calculation is either ‘eyeballed’ by the marker (sort of “thats probably close enough”!) or typed into a spreadsheet for verification (sort of “four point three five, no, four…”)…. I’m really keen on identifying if a student has gone wrong in their calculations, so I’m the typing guy, but either way is a hassle and can lead to errors.

These assignments are also submitted to our virtual learning environment as Word files: students need to write text and add images, structures and diagrams to their report. We did the first formative lab assignment in Excel, assuming that we could cut and paste the ‘calculation block’ into the students submitted spreadsheet, but some students added units to their Excel cells, some changed the formatting of the cells, some added % units (which Excel recognises as a number). (We locked the worksheet but students needed control of the worksheet formatting so they could enter the correct number of significant figures in their values.) This showed that using Excel wasn’t as easy as I’d hoped, and wouldn’t work long term.

In the latest assignments (submitted as a Word file), students submitted their calculations in a table. I’d designed a ‘calculation worksheet’ in Excel to allow me to verify the student’s maths after I’d copied and pasted the Word table into Excel. Overall the calculation block worked well, but I had several problems with ‘copying and pasting’. The units issue (mentioned above) was even more of a problem: but really, I’ve only got myself the blame! I’d been telling the class that they *must* add units to their calculations, and they’ve taken me at face value…there’s units everywhere now! It probably takes 2 or 3 minutes to manually delete the letters from the pasted text to generate Excel-compatible numbers for every assignment. It mounts up (and is boring and frustrating – not good when your marking!) I can’t tell the students to stop using units now because it’s messing up my spreadsheet: units are important in communicating quantitative calculations, they need to stay. The other issue is that the Word tables contain a number, but they may also contain spaces, or carriage returns. These throw off the ‘pasting’ into Excel, so the data ends up in unexpected rows: another 2 or 3 minutes per assignment needs to be spent sorting and re-aligning the data so the verification calculations can get the right variables.

So here the two coding solutions I’ve come up with…

Pasting and aligning the data.

The first trick is to cut the data from the Word table into the Excel spreadsheet. It took me a while to find how to delete blank cells from a selected range of cells. It turns out you can do this manually by selecting the cells you want to condense, use the ‘Find and Select’ button and choose the ‘Go to Specials’ option. In the box that opens, select the ‘Blanks’ option. When you return to the worksheet, right click on one of the highlighted blank cells, delete the cells and Shift the cells up.

The marco below will do both these things at once. I run it by copying the relevant parts of the Word table, then selecting the cell where I want to place the top left hand datapoint, and then clicking an on-worksheet button to paste (and sort) the data.

Sub Delete_blank_cells2()
'This will paste the contents of the clipboard
'(which should be columns from a word table)
'into the activated/selected cell in the sheet
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
        False, NoHTMLFormatting:=True
 'This will take the cells that have just been pasted
 'into the sheet and delete the empty cells.
    Selection.Delete Shift:=xlUp
End Sub

Removing the units.

There are two different issues in removing units. If a student adds a ‘normal’ unit to the end of the value (i.e grams), that means Excel will see the contents of the cell as a string (or text) and not as a number. I came across the VBA code below online here.

Function StripChar(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = .Replace(Txt, "")
End With
End Function

However, this code defines a function which you can type into a Excel spreadsheet just like any formula: but it didn’t work for my purposes because it removes all the non-numerical symbols (that great for 10 grams, but not for 10.25 grams!). The term “\D” in the line .Pattern = “\D”is a Regex terms for ‘every character except numerical digits‘, so I needed to change that. After some digging I came up with “[^0-9.-]” which means everything negated (or not) 0 to 9, the decimal point or the negative (-). So my final VBA looks like this:

Function StripChar(Txt As String) As String
' function VBA inspired by
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[^0-9.-]"
StripChar = .Replace(Txt, "")
End With
End Function

If you want to use this code in your Excel workbook copy the above text into your VBA module window. (If your not sure how to do this there’s instructions here.)

The other issue is the use of percentgages (as %). Excel treats the % format as a number (not a string), which it multiplies by 100 and sticks the units ‘%’ at the end for the reader: for example, the value 0.96 is shown as 96% . However, 96 (the number without % after it) is also an acceptable number to have in the Word table, because the ‘% units’ are specified in the column (or row) headers: in this case the value 96 is represented by the number 96. Removing the units needs to deal with both these different ways of presenting percentages.

Here I’ve used the CELL(“format”) function to get Excel to tell me if the cell is formatted as an Excel % number. Cells formatted as a percentage are coded P#, where # represent sthe number of decimal places. If the first letter of the cells format beginns with P (given by the LEFT function) it’s an Excel percentage and needs to be multiped by 100 to change it to a simple value. So the overall formula for stripping the units out of a string or percentage, or returning a number, from cell I28 is:


Note that the StripChar VBA function above need to be set for this to work. It doesn’t look like I can get the cell format ‘passed as a variable’ from the worksheet to the function VBA, so I have to keep this long formula on the worksheet.

Categories: Tags: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s