Function Problems

How to Make Excel TEXT Function Use Regional Decimal and Thousands Separators

Rana Garcia Oct 10, 2026 870 views

Question details

The user needs to apply system or Excel-configured regional decimal and thousands separators dynamically within the TEXT function or LAMBDA functions.

How to Make Excel TEXT Function Use Regional Decimal and Thousands Separators

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating localized formulas or custom LAMBDA functions that need to dynamically adapt to regional number formatting settings.
Observed behavior: Excel's TEXT function and worksheet formulas cannot directly query or automatically expose the decimal and thousands separators configured in Windows or Excel Options.

Before you start

Confirm your current Windows regional settings and Excel's advanced options for decimal and thousands separators before attempting to apply dynamic formatting formulas.

Solution 1Recommended

Use VBA to Retrieve Regional Settings

Since worksheet formulas cannot directly access system separators, a custom VBA function is the most reliable way to retrieve and use these settings in your workbook.

Excel provides the Application.International property in VBA, which allows you to extract regional settings like the list, decimal, and thousands separators to use dynamically in your workbook formulas.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.

Create a New Module

In the top menu, click 'Insert' and select 'Module' to create a new blank module for your custom functions.

Write the Custom Function

Create a VBA function using Application.International(xlDecimalSeparator) and Application.International(xlThousandsSeparator) to return the current system characters to your spreadsheet.

Save and Apply

Save your document as a Macro-Enabled Workbook (.xlsm) and call your new custom function within your standard worksheet formulas.

Macro Security: Ensure that macros are enabled in your Excel Trust Center settings for custom VBA functions to run properly.

Solution 2

Manually Define Locale-Specific Number Formats in TEXT Function

If you cannot use VBA, you must manually hardcode the specific decimal and thousands separators corresponding to the target locale within the TEXT function.

Free Microsoft Office alternative

Use WPS Office for Highly Compatible Spreadsheet Management

While standard worksheet formulas in most spreadsheet software cannot dynamically query Windows regional settings, WPS Office provides a fully compatible, lightweight, and free alternative to Microsoft Office. It seamlessly supports advanced VBA macros and localized number formatting.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx or .xlsm).
3. Enable and Run Macros: Access the Developer tab to seamlessly enable macros and run your custom regional formatting VBA code.

Free to use with a lightweight installation packageHigh compatibility with Microsoft Excel (.xlsx, .xlsm, .csv) formatsSupports VBA and advanced macro functions to handle regional settingsFamiliar, tabbed user interface for easy transition

Frequently Asked Questions

Does the NUMBERVALUE function help retrieve regional separators?

The NUMBERVALUE function converts text to a number in a locale-independent way, but it does not provide a method for formulas or LAMBDAs to retrieve or display the current system separators.

Can LAMBDA functions query Excel's regional display settings?

No, this is a limitation of worksheet formulas. LAMBDA functions cannot directly query the Excel environment or system for regional settings.

How do I find out what my current Excel separators are?

You can view or change the decimal and thousands separators in Excel by navigating to File > Options > Advanced, and looking under the 'Editing options' section for the 'Use system separators' checkbox.

Excel & Data Problems