How to Combine Two Excel Columns on Another Sheet in WPS Office
Question details
The user needs to join text values from two Excel columns as a single text string and display the result on a separate worksheet, without performing mathematical addition.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Combine values such as first and last names from two columns and return the combined text on another sheet.
- Observed behavior
- A formula is needed to reference the source sheet and concatenate the two columns into readable text on the destination sheet.
Open the workbook in WPS Office Spreadsheets and note the exact source sheet name and the column letters you want to combine. If the sheet name contains spaces or special characters, it must be wrapped in single quotes in the formula.
Concatenate Two Excel Columns on Another Worksheet with the Ampersand Formula
Use this solution when you want to combine two column values into one text string on a separate sheet and keep the result linked to the source data.
This method uses the ampersand operator to concatenate values across sheets. It works for text, numbers, and dates, and it does not change the original data.
Click the sheet tab where the combined result should appear, or create a new sheet by clicking the plus (+) button next to the sheet tabs in WPS Office Spreadsheets.
Click the cell where you want the combined text to start, for example C2.
Type =Sheet1!A2&Sheet1!B2 to join the values without a separator, or =Sheet1!A2&" "&Sheet1!B2 to add a space, then press Enter.
Select the cell, then drag the fill handle in the lower-right corner down to copy the formula to the remaining rows.
If your source sheet or columns differ, replace Sheet1, A, and B with the actual sheet name and column letters.
Combine Two Excel Columns on Another Sheet with WPS Office Spreadsheets
In WPS Office Spreadsheets, open the workbook, select the destination sheet, and use the same concatenation formula you would use in Microsoft Excel. The formula joins the two columns into one text string and updates when the source cells change.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the workbook that contains the two columns to combine.
- 2. Select the destination sheet: Click the tab of the sheet where the combined result should appear, or click the plus (+) icon to create a new sheet.
- 3. Enter the concatenation formula: In the first result cell, type =Sheet1!A2&" "&Sheet1!B2 and press Enter. Replace Sheet1, A, and B with your source sheet and columns.
- 4. Apply the formula to the rest of the rows: Drag the fill handle at the lower-right corner of the cell down to copy the formula to the remaining rows.
- 5. Save the result: Press Ctrl+S to save the workbook. The .xlsx format remains compatible with Microsoft Excel.
Frequently Asked Questions
Can I combine two columns from different sheets, not just another sheet?
Yes. Use the sheet name before the cell reference for each column, for example =Sheet1!A2&" "&Sheet2!B2. If a sheet name contains spaces, wrap it in single quotes, such as ='Sheet 1'!A2&" "&'Sheet 2'!B2.
How do I add a separator between the two values?
Place the separator between double quotes in the formula. For a space use =Sheet1!A2&" "&Sheet1!B2; for a comma and space use =Sheet1!A2&", "&Sheet1!B2.
Why does the formula return #REF! or #NAME? instead of text?
The error usually means the sheet name is misspelled or a sheet name with spaces is not enclosed in single quotes. Use the exact sheet name, for example ='Sheet 1'!A2&" "&'Sheet 1'!B2.
Can I combine more than two columns using this method?
Yes. Continue adding ampersands and cell references, for example =Sheet1!A2&" "&Sheet1!B2&" "&Sheet1!C2. Include a separator between each column if needed.
Does this formula update automatically if the source cells change?
Yes. Because the formula references the source cells, the destination sheet updates automatically when the values in Sheet1!A2 or Sheet1!B2 change.
