Formula Errors

How to Fix Excel Formula Errors When Removing Trailing Commas

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to fix an Excel formula that worked in Excel 2016 but returns an error in Microsoft 365, specifically while attempting to concatenate cells and remove trailing commas.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Migrating a spreadsheet from Excel 2016 to Microsoft 365 and performing text concatenation with comma delimiters.
Observed behavior: The formula produces a syntax or execution error in Microsoft 365. Changing the decimal separator alone fails to resolve the issue.

Before you start

Before modifying your formula, check your computer's Regional Settings to verify whether your default list separator is set to a comma or a semicolon.

Solution 1Recommended

Verify and Update Regional Argument Separators

Ensure the formula uses the correct argument separator dictated by your operating system's regional settings, which is often the cause of cross-version errors.

When moving files between different computers or software versions, the expected list separator might change based on the system's Regional Settings. If your system expects semicolons, using commas in your formula arguments will trigger a syntax error.

Check system settings

Open your system's Control Panel, navigate to Region, click on 'Additional settings', and locate the 'List separator' value.

Update the formula separator

If the list separator is set to a semicolon (;), replace the commas (,) separating the arguments in your failing Excel formula with semicolons.

Test formula components

Isolate and test each part of the formula separately to ensure the cell references and syntax are correct under the new Microsoft 365 environment.

Decimal Separators: Changing the decimal separator alone will not fix argument separation errors. The list separator is what dictates how formula arguments are divided.

Solution 2

Manually Remove Trailing Commas

Directly edit the text string to remove commas if complex extraction formulas are failing.

Efficient Formula Management

Use WPS Spreadsheet for Seamless Formula Compatibility

WPS Office Spreadsheet provides excellent compatibility with advanced formulas and text manipulation tools. You can easily concatenate cells or remove trailing characters using modern functions like TEXTJOIN without encountering version-specific errors.

1. Open your spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx file.
2. Select the target cell: Click on the cell where you want to output the clean, concatenated data.
3. Apply TEXTJOIN function: Type '=TEXTJOIN(",", TRUE, A1:A5)' (adjusting the range as needed). This combines the cells with commas while automatically ignoring empty values, preventing trailing commas entirely.
4. Press Enter: Hit Enter to execute the formula and view the cleanly formatted text.

Highly compatible with Microsoft Excel (.xlsx) formats and standard formula syntaxSupports advanced text functions like TEXTJOIN to prevent trailing commas automaticallyIntuitive interface for formula auditing and error checkingFree to download and lightweight on system resources

microsoft office alternative - wps office

Frequently Asked Questions

Why does a formula work in Excel 2016 but return an error in Microsoft 365?

This usually happens due to changes in default regional settings on the new computer, or conflicts with new dynamic array behaviors introduced in Microsoft 365. Verifying your list separator (comma vs. semicolon) is the best first step.

How do I automatically remove a trailing comma using a formula?

You can use the IF and RIGHT functions to check if the last character is a comma. For example: =IF(RIGHT(A1,1)=",", LEFT(A1,LEN(A1)-1), A1) will strip the trailing comma if it exists.

Can I change my argument separator from semicolon back to comma?

Yes, but this requires changing your operating system's regional settings. In Windows, go to Control Panel > Region > Additional settings, and change the 'List separator' from a semicolon to a comma.

Excel & Data Problems