How to Identify Excel Accounting Formats for Calculations
Question details
The user needs a way to identify the applied accounting format or currency symbol for each row in a spreadsheet before performing calculations.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing mixed-currency accounting data for calculations where knowing the underlying currency format of each cell is required.
- Observed behavior
- Standard formulas extract the raw underlying value, completely ignoring the applied cell formatting, making it difficult to differentiate between various currencies dynamically.
Ensure your workbook is saved in a macro-enabled format (.xlsm), as both the legacy GET.CELL function and custom VBA User-Defined Functions require macro execution permissions to work correctly.
Use the Legacy GET.CELL Function via Name Manager
Utilize the Excel 4.0 Macro function GET.CELL to extract the formatted cell text, then strip out numbers and punctuation using nested SUBSTITUTE formulas.
The GET.CELL function is a legacy Excel 4.0 macro command. It cannot be typed directly into a cell; instead, it must be defined as a named range. It can read exactly how a cell is formatted, making it highly effective for extracting currency symbols.
Open your workbook and select the cell directly next to your first accounting value (for example, select B1 if your value is in A1).
Navigate to the Formulas tab on the ribbon and click on 'Name Manager', then click the 'New' button to create a defined name.
In the Name box, type 'GetAccounting'. In the 'Refers to' box, enter this formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(GET.CELL(53,!A1)," ",""),",",""),".",""),"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")
Click OK to save. Back in your worksheet, type =GetAccounting in cell B1 and press Enter. Drag the fill handle down to apply this logic to the rest of your data rows.
Create a Custom VBA User-Defined Function
Write a short VBA script to programmatically read the displayed text of a cell and remove all digits, leaving only the accounting symbols.
Handle Complex Accounting Formats with WPS Spreadsheet
WPS Spreadsheet offers robust support for advanced data processing, including full compatibility with VBA macros and legacy defined name functions, helping you identify and manage accounting formats easily without errors.
- 1. Install WPS Office: Download and install the free WPS Office suite from the official website.
- 2. Open your macro workbook: Launch WPS Spreadsheet and open your .xlsm file containing the mixed accounting formats.
- 3. Utilize Name Manager or VBA: Navigate to the Formulas tab to configure your defined names, or press Alt+F11 to easily access the built-in VBA Editor.
Frequently Asked Questions
Why can't I just use the standard CELL function instead of GET.CELL?
The built-in standard formula =CELL("format", A1) returns specific formatting codes (like "C2" for currency), but it does not distinguish between different currency symbols applied via custom accounting formats. GET.CELL(53, A1) retrieves the exact raw display string.
Can I extract the currency symbol without enabling macros?
Without macros or VBA, native Excel formulas cannot easily read the display formatting (like currency symbols) if they are applied via cell formatting rather than typed directly as text. You would need to rely on standardizing your data first or using Power Query.
Why does my GET.CELL formula return a #BLOCKED! or #NAME? error?
This usually happens because Excel blocks Excel 4.0 macros by default for security reasons. You must enable macros in your Trust Center settings, specifically allowing Excel 4.0 macros, and ensure the file is saved as an .xlsm macro-enabled workbook.
