A Free Excel Add-in Function: Convert Shorthand Notation
Because A Smart Student Asked An Insightful Question
In business reports, large numbers are often reported in numeric shorthand notation like $2.5B or USD25M. While we can use the format cells dialog to create a custom number format, the majority of data we may import from other data sources will include a letter, which converts numbers to text when used in Excel.
In the process of teaching my classes, a smart student asked: Why isn’t there an easy way for Excel to allow me to convert $2.5B to a number like 2,500,000,000 that I can use in my formulas. This insightful question led me to develop a new Excel function I call Convert Shorthand Notation, or CSN. This free custom Excel feature adds functionality to your Excel workbooks to simplify your workflow.
I hope you find this feature useful. Please let me know if you feel it can be improved with additional functionality or features.
To Install The CSN Add-in:
- Excel Add-in files are typically stored in the following directory:
However, you can save this file anywhere on your computer or network, but in a location that should not change and one where you may remember where it is located.
- In Excel, choose the File tab > Options Command.
- In the Excel Options dialog, along the left, select the Add-Ins category.
- To the right, locate and select the Go… button.
- Click the Browse… button.
- Browse to the location where you saved the Add-In.
- Click the OK button 2-3 times to close all dialogs.
How To Use the Excel CSN Function:
Note that this Add-In will not modify the ribbon. To use the feature in a cell, type =CSN( and then reference a cell with a value (or you can also type a value directly) to covert from shorthand text to a longer numeric value. The CSN function can also be nested inside other functions like VLOOKUP, XLOOKUP or MATCH for the Lookup_Value, in an IF or IFS function as part of a logical test condition, or in a SUMIFS function as part of a criteria.
Note: If you share your workbook with other users who have not installed the Add-In, they can see the result of your formula, but if they change values and recalculate the workbook those formulas will fail without the Add-in. The Add-In feature is globally available to every workbook on your computer but does not travel with the workbook if you email the file.
If you would like to learn more about creating custom User Defined Functions (UDFs) with Excel, I would encourage you to sign up for our 48-hour Live VBA Training Online Webinar
- By using this add-in you agree that you understand:
The data is provided under GNU public license for educational and entertainment purposes only. For more information about the GNU public license please visit: https://www.gnu.org/licenses/gpl-3.0.en.html
- You agree not to re-sell this product.