Spreadsheet problem generator part II: formatting units with special characters
Posted by Greten on 06 Jul 2019 under Tools
Numbers can be written in several different formats. The format they are written usually depends on the traditions or standards of the society or the industries that are using them. While 20% and 0.2 are equal, you would not hear any department store announcing "0.2 discount today"; it's always something like "20% discount today".
In working with science and mathematics problems, there are different formats of numerical data. Some quantities are given in terms of fraction or percent. Some problems will provide specific times as seen on clocks instead of giving the number of hours or minutes. In constructing a spreadsheet problem generator, you will encounter the need to format numerical data in more than one point.
The previous entry on how to create a spreadsheet problem generator provided an introduction on how to create spreadsheet problem generators. However, the examples shown in that entry can accept only numerical values that are in decimal format. This post will explain how to format the cells and enter your word problem so that other possible numerical values are formatted accordingly.
This post will provide examples for Google Sheets, LibreOffice Calc 6.2.2.2, and MS Office 2016.
Formatting units with exponents and special characters
This is possibly the easiest formatting because you are not concerned with any of the numerical data that you must tweak to come up with a new problem. Most units are easy to encode because they are simply letters or symbols placed after a number. However, some units are difficult to enter because either they are not on the keyboard, or of limitations of the spreadsheet application.
Some of the units that you might find difficult to enter are as follows:
- The unit Angstrom (Å), unit of length equal to 1/10,000,000,000 or 1.0 × 10-10 of a meter.
- The prefix symbol for micro in SI is the Greek letter mu (µ).
- Units that contain superscripts such as 2 or 3 (e.g., m/s2 and cm3)
Units in Excel and Calc
In Calc, you can insert special characters for units such as Angstrom (Å) and the SI prefix micro (µ) using Insert » Special Character. It will open the Special Characters windows.
Calc's special characters come with several Greek symbols, numerical superscripts, and other assorted characters that you can use to write units that include these characters. The search function associates some characters with some of the known units of measurement. For example, typing both "mu" and "micro" will narrow the special character matrix to Greek letter μ. The same result also happens to Ω if you searched for either "omega" or "ohm". However, Å will not filter-in if you search "angstrom" even though the character exists among the special characters.
The exponents of all numerals from 0 to 9 also exist as special characters. You can use them if you write units with exponents such as cm3 (cubic centimeter) and m/s2 (meter per second squared). You can narrow down the special character matrix to display them by typing "superscript".
In Excel, you can insert special characters by selecting Insert » Symbol in the ribbon menu. It will open the Symbols windows.
Unlike in Calc, there is no search field in Excel's Symbols window. You need to browse all of the characters or use the Subset dropdown menu to look for the specific character that you need.
Another option you have in Excel and Calc is to format a character within a cell to superscript or subscript; highlight the character, right-click on it, and select Format Cells for Excel or Character for Calc. Both options will open a window that provides you with several character formatting options. However, you can do this only in cells that contain only text; you cannot do this in numbers, formulae, concatenations, and anything that starts with an equal sign.
Download here examples of Calc and Excel spreadsheets that use special characters and superscripts as units.
Units in Google Sheets
The insertion of a special character is not readily available in Sheets. Unlike in Excel and Calc, if you click the Insert in the top bar menu, you will not find anything similar to Symbol or Special Character. To get the special characters, you need to open Google Docs in a separate browser or tab and copy the special character from Docs to Sheets.
You can open the Insert special characters window by selecting Insert » Special characters in the top bar menu. Similar to Calc, the Insert special characters comes with a search field.
The search function seems to associate more keywords as compare to the one in Calc. You can enter keywords like Angstrom, superscript, or anything that can be associated with a particular character you are looking for. Then, click the character you need on the matrix on the left. Close the Insert special characters window. You can now copy the special character from Docs to your spreadsheet.
Click here to see a Google sheet problem generator that uses special characters and superscripts as units.
This post is the second of the four-part post series. Here is the complete list of all the posts under this series.
- Spreadsheet problem generator part I: basic word problem generator
- Spreadsheet problem generator part II: units with special characters
- Spreadsheet problem generator part III: concatenate cells with special number format
- Spreadsheet problem generator part IV: formatting date and time
This post was updated just three months ago, July 6. I am updating it again to include Google Sheets. I opted to update this post instead of writing a separate post just for Google Sheets to make it similar with parts I and and the former III (now part IV) that covered Excel, Calc, and Sheets. However, as this post expands, I realized that it would be better to split the special characters from special number formatting and TEXT function. Hence, what's left here is about the units with special characters. The post about special number formatting is the new part III, while the post about date and time became the part IV.
Last updated on 06 Oct 2019.
Learn from others
Greten says: Hello Jayna, thanks for dropping by.
You are referring to the superscripts that exist for some numbers as separate characters right? That’s what I would like to introduce about formatting in Google Sheets, but I consider this as last resort option because not all numbers have equivalent superscript as separate characters.
Share your thoughts
* Required. Your email will never be displayed in public.
One extremely useful trick I have not seen described elsewhere is inserting the units of a number within the cell containing the number. LibreOffice Help tells how to do this (” text” in the number format), but not how to get superscripts (so one can have “6 cm³”, rather than “6 cm3” or “6 cm^3”, etc.
The trick is to copy the superscript from the special characters menu. It took me awhile to think of that!