How to Convert Numbers to Words Using Lakh and Crore in Excel
Question details
The user wants to convert numeric values into written text amounts based on the South Asian numbering system (lakhs and crores) instead of the standard millions and billions.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating financial reports, invoices, or accounting documents where the regional numbering standards for Bangladesh, India, or Pakistan are required.
- Observed behavior
- The default SpellNumber function or number formatting in Excel natively supports only Western numbering systems (millions/billions), necessitating a custom LAMBDA function or VBA script.
Verify your Excel version before proceeding. The LAMBDA function approach requires a Microsoft 365 subscription; if you are using an older version, you must use the VBA macro solution.
Use a Custom LAMBDA Function (For Microsoft 365)
Implement a custom LAMBDA function to convert numbers directly in modern versions of Excel without relying on macros.
The LAMBDA function allows you to create customized, reusable functions directly within Excel's Name Manager. This method is highly recommended for Microsoft 365 users because it avoids the security prompts and file format changes associated with VBA macros.
Navigate to the Formulas tab on the Excel ribbon and click on the Name Manager button.
Click 'New', type 'SpellNumberBD' into the Name field, and paste your custom LAMBDA formula (handling the lakh and crore conversion logic) into the 'Refers to' box.
Select an empty cell in your worksheet and type =SpellNumberBD(A1), substituting 'A1' with the cell reference containing your numeric value.
Implement a VBA Macro for Lakhs and Crores
Create a User-Defined Function (UDF) via Visual Basic for Applications (VBA) to translate numbers into words, compatible with older versions of Excel.
Easily Format Currencies to Words with WPS Office
WPS Spreadsheet offers powerful, built-in support for advanced functions and VBA macros. You can effortlessly run custom User-Defined Functions to format your regional financial reports using lakhs and crores without needing a premium subscription.
- 1. Download and Open WPS Office: Launch WPS Spreadsheet and open the document containing your financial data.
- 2. Access the VBA Editor: Press ALT + F11 to open the built-in VBA editor directly within the software.
- 3. Insert the Code: Insert a new Module, paste your customized SpellNumber VBA script, and close the editor.
- 4. Convert the Numbers: Type the formula =SpellNumber(A1) directly into any cell to seamlessly view the results formatted in lakhs and crores.
Frequently Asked Questions
Why doesn't the default SpellNumber function work for Indian Rupees?
The standard SpellNumber macro provided by Microsoft uses Western numbering conventions (millions and billions). To output amounts in lakhs and crores, you must implement a customized logic script via a LAMBDA formula or a tailored VBA macro.
Can I use the LAMBDA function in older standalone versions of Excel?
No, LAMBDA functions are an exclusive feature of Microsoft 365 subscriptions. If you are using standalone versions like Excel 2019, 2016, or earlier, you must rely on the VBA macro approach to create custom functions.
Do I need to save my file differently if I use the VBA method?
Yes. Whenever you add VBA code to a workbook to convert numbers to words, you must save your spreadsheet as an Excel Macro-Enabled Workbook (.xlsm). If saved as a standard .xlsx file, the custom code will be permanently deleted upon closing.
