How to Fix Excel Concatenation Errors with Quotes and Regional Settings
Question details
The user needs to combine Excel cell values with a space and is encountering formula syntax errors related to quotes and regional argument separators.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining data from multiple cells into one target cell with a space separator.
- Observed behavior
- Formulas return syntax errors when using commas to separate arguments, often due to regional settings requiring semicolons instead.
Check your computer's regional settings to determine whether your system uses commas or semicolons as list separators, as this dictates your formula syntax.
Use the Ampersand (&) Operator to Combine Cells
The most universal method to concatenate cells, bypassing many regional formula separator issues by not requiring commas or semicolons.
Using the ampersand (&) operator is often the easiest way to combine text because it does not rely on function argument separators, which vary by region.
Click on the cell where you want the combined text to appear.
Type =C4&" "&B4 into the formula bar, replacing C4 and B4 with your actual cell references. The " " inserts a space between the values.
Press Enter to execute the formula and view the combined result.
Use the CONCAT Function with Correct Regional Separators
Utilize the CONCAT function while adjusting the argument separators for your specific regional settings.
Use WPS Office to Seamlessly Combine Cell Data
WPS Spreadsheet fully supports standard concatenation functions like CONCAT and the ampersand operator, automatically adapting to your regional formatting to make merging data effortless.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the data you want to merge.
- 2. Select the target cell: Click on the blank cell where the combined result should appear.
- 3. Enter the formula: Type =A1&" "&B1 to merge data from cells A1 and B1 with a space in between.
- 4. Fill down the column: Press Enter, then double-click the fill handle in the bottom-right corner of the cell to apply the formula to the rest of your data.
Frequently Asked Questions
Why does Excel show a formula error when I use commas in CONCAT?
If your computer's regional settings use a comma as the decimal separator (common in many European countries), Excel will typically require a semicolon (;) as the list separator in formulas to avoid confusion.
How do I add a space between combined words in Excel?
You need to include a space enclosed in double quotes (" ") within your formula. For example, using =A1&" "&B1 will join the contents of A1 and B1 with a space in the middle.
Can I change the argument separator from semicolon back to comma?
Yes. You can change this by modifying your operating system's regional settings. On Windows, go to Control Panel > Region > Additional settings, and change the 'List separator' character to a comma.
