'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel TEXT Formula Decimal and Thousands Separators
Question details
The user needs to correct the decimal and thousands separators used by the Excel TEXT formula for currency formatting, which changed unexpectedly.
- Product
- Office 365 Excel
- Device & OS
- Windows
- Scenario
- Applying a TEXT formula for local currency formatting (such as Turkish Lira) after an Office 365 installation or configuration update.
- Observed behavior
- The Excel TEXT formula displays the wrong decimal and thousands separators instead of the previously functioning format.
Verify your preferred local currency format structure (e.g., #.##0,00 TL for Turkish formatting) and save your workbook before modifying application or system settings.
Adjust Separator Settings in Excel Options
Manually override or reset the decimal and thousands separators directly within Excel to quickly fix how TEXT formulas format numbers.
Excel typically uses the system's regional settings by default. If an update disconnected these settings, you can manually force Excel to use the specific separators you need.
Launch Microsoft Excel, click on 'File' in the top-left corner, and select 'Options' located at the bottom of the sidebar.
In the Excel Options dialog box, click on 'Advanced' in the left-hand navigation pane.
Scroll down to the 'Editing options' section. Look for the checkbox labeled 'Use system separators'.
Uncheck 'Use system separators'. Set the 'Decimal separator' to a comma (,) and 'Thousands separator' to a period (.) for Turkish formatting. Click 'OK' to apply.
Update Windows Regional Settings
Change the system-wide regional settings in Windows to ensure Excel automatically inherits the correct default currency and number formats.
Use WPS Office to Easily Manage Number and Currency Formatting
WPS Office provides a highly compatible and user-friendly spreadsheet tool that makes adjusting decimal and thousands separators straightforward. You can easily apply custom formatting or sync with your system's regional settings without hassle.
- 1. Open Spreadsheet: Launch WPS Office and open your spreadsheet document.
- 2. Access Options: Click the 'Menu' button in the top-left corner and select 'Options'.
- 3. Navigate to Edit Settings: Select the 'Edit' tab within the Options dialog box.
- 4. Adjust Separators: Find the 'Use system separators' option, uncheck it to enter your custom decimal and thousands separators, and click 'OK'.
Frequently Asked Questions
Why did my Excel TEXT formula separators change after updating Office 365?
Office 365 updates can sometimes reset Excel's advanced configurations or cause the application to temporarily lose sync with your Windows regional settings, reverting your numbers to the default US formats (using a period for decimals and a comma for thousands).
How do I write a TEXT formula for Turkish Lira?
Assuming your system separators are set correctly for the Turkish locale, you can use the formula =TEXT(A1, "#.##0,00 TL"). If your system uses US separators and you have not overridden them, you would need to use =TEXT(A1, "#,##0.00 TL") instead.
Does disabling 'Use system separators' affect my other Excel workbooks?
Yes. Unchecking 'Use system separators' and manually defining the decimal and thousands separators in Excel Options applies to the entire Excel application. This changes how numbers are displayed across all your open and future workbooks.
Will changing Windows regional settings affect other applications?
Yes. Modifying your Windows regional settings will change the default number, currency, date, and time formats for all applications running on your Windows device, not just Microsoft Excel.
