You can enter an ASCII character in Excel (if you know the ASCII code) by holding down the Alt key and entering the decimal code value on the numeric key pad. This has two problems:
- It doesn’t work in Excel for the extended Unicode character set.
- Depending on your regional settings, you may get inconsistent results.
This post presents some alternatives for entering symbols from the vast Unicode collection in Excel. Open source VBA code and examples can be found in:
The Unichar function was introduced in Excel 2013. This function returns the Unicode symbol for any given decimal code value. In the Unicode spreadsheet I have used this function to generate a table of values and symbols for any of the lists of mathematical operators and symbols given by Wikipedia at Mathematical Unicode Symbols.
The screen-shot below shows the list of pre-defined tables (click on any image for full size view):
Enter 1-16 in cell A26 to generate one of the 16 pre-defined tables:
Or enter any hex value greater than 16 (up to the upper limit of Unicode values) to generate a table of symbols starting from that value:
The Unichar function allows Excel to generate any Unicode symbol, but it does require looking up (or learning) the correct decimal code for any symbol you might need.
For those using Excel 2010 or earlier, or if you don’t have access to the code value for the symbol you want, you can insert Unicode symbols from the Insert-Symbol dialog, on the Insert Tab, but this is a slow process, especially if you need to search for the particular symbol you want. A more convenient approach was suggested at an Eng-Tips discussion, linking to excel-tips.blogspot.com. It is possible to add any desired symbol to the Excel auto-correct list (see the excel-tips link for details), so symbols can be easily generated by entering an easy to remember short-cut code, such as (deg) for the degree symbol.
Adding a large number of symbols to the aut0-correct list is also pretty laborious though, so I have combined this feature with the UniChar function and some VBA code to allow any number of short-cuts and symbols to be added automatically:
Clicking the “Update Auto-correct” button on the download spreadsheet will update the list (for those with Excel 2013 or later), and the short-cuts listed will then be available from any Excel spreadsheet, or any other Office application. Note that:
- The Unicode spreadsheet list includes 24 mathematical operators, plus the Greek alphabet in upper and lower case.
- The macro automatically surrounds the short-cuts with (), so for instance to generate the sum symbol enter: (sum).
- To extend or edit the list, just enter the short-cut and the symbol decimal code value, and adjust the named range “UCList” to the new extent of the two columns. The symbol in the third column is for reference, and to check that the code number is correct, but is not required for the macro to work.
- Symbols may also be removed from the Auto-correct list by adding them to the UCListold range, adjusting the named range extent, and clicking the Remove-Auto-correct button.
The spreadsheet shows an example of the revised auto-correct in operation. Entering:
| Cos(30(deg)) . (alpha)(+-) (int) ((beta) * x(^2)) (>=) (omega_) |
displays as:
| Cos(30°) . α ± ∫(β * x²) ≥ Ω |
If an unintended auto-correct is applied just press Ctrl-Z immediately, the text will then revert to the characters actually entered. This applies to all auto-corrects incidentally, including such annoying ones as removing the second upper case character when you enter units such as MPa.
As an alternative to entering the short-cuts including the surrounding (), the Unicode spreadsheet also includes a UCode user defined function (UDF), that will convert all text in the UCList table to the associated symbol (in Excel 2013 and later). So if:
| Cos(30deg).alpha+- int (beta * x^2) >= omega_ |
is entered in cell H16, it can be converted to:
| Cos(30°).α± ∫ (β * x²) ≥ Ω |
with the function: =UCode(H16).
To convert the UDF to a text string: press Edit (F2), then Re-calculate (F9) and enter.
This UDF of course only works in the Unicode spreadsheet (or if the function VBA code is copied to another spreadsheet). Note that the UDF does not use the Excel Auto-correct list, it uses the UCList range on the spreadsheet. If you don’t want to change the Auto-correct list, the UDF will still work.
Newton Excel Bach, not (just) an Excel Blog 
Hey thanks Doug, that’s handy. I wrote a macro to access symbols I commonly use through a userform, but this is far simpler.
You mentioned in your post the frustration at Excel autocorrecting MPa to Mpa. I suspect you’re already aware of this, and were just using this as an example, but for the benefit of others that may be reading: you can prevent this by adding MPa (and GPa and similar) to your list of exceptions to prevent Excel autocorrecting these.
Go to File → Options → Proofing → AutoCorrect Options
Then under the AutoCorrect tab, click the Exceptions.button
Select the INitial CAps tab, then add your exceptions.
LikeLike