How to Insert Commas Between Excel Cell Values Using TEXTJOIN
Question details
The user needs to combine numbers or text from multiple separate cells into one single cell, formatted as a comma-separated list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating and formatting data spread across multiple cells into a single string for easier copying, exporting, or reporting.
- Observed behavior
- Values exist in separate cells (e.g., A1:A3) and need to be merged into one continuous comma-separated list without having to type commas and combine them manually.
Identify the exact range of cells you want to merge, and check if your computer's regional settings use commas or semicolons as decimal marks, as this affects the required syntax for formulas.
Use the TEXTJOIN Function to Combine Cells
The TEXTJOIN function is the most efficient method for merging multiple cell values with a specific delimiter while automatically skipping empty cells.
By specifying a comma as your delimiter, TEXTJOIN merges an entire range of cells into one string effortlessly. It also features a built-in argument to ignore blank cells, preventing awkward double commas in your final text.
Click on an empty cell in your spreadsheet (for example, C1) where you want the final comma-separated list to be displayed.
Type the formula `=TEXTJOIN(",", TRUE, A1:A3)` into the formula bar, replacing A1:A3 with the actual range of cells you want to combine.
Press the Enter key on your keyboard to execute the formula and instantly view the combined, comma-separated values.
Use the Ampersand (&) Operator for Older Versions
If you are using an older spreadsheet version that does not support the TEXTJOIN function, you can manually combine cells using the ampersand symbol.
Combine Excel Cell Values Effortlessly with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced data functions like TEXTJOIN, making it incredibly simple to combine and format multiple cell values with commas automatically.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing the data you wish to merge.
- 2. Input the formula: Select a blank cell and type `=TEXTJOIN(",", TRUE, A1:A3)`, adjusting the formula to highlight your specific data range.
- 3. Get instant results: Press Enter to instantly merge the cells into a clean, comma-separated list.
Frequently Asked Questions
What does the 'TRUE' argument in the TEXTJOIN formula do?
The 'TRUE' argument instructs the function to ignore any blank cells within your selected range. This ensures your final text doesn't end up with consecutive commas if a cell in the middle of your data happens to be empty.
Why am I getting an error when typing the TEXTJOIN formula?
This is often due to regional format settings on your computer. If your system uses a comma as a decimal point, you must use semicolons to separate formula arguments. Try writing your formula as `=TEXTJOIN(","; TRUE; A1:A3)` instead.
How can I add a space after the comma in the combined list?
To add a space after the comma, simply include a space inside the quotation marks of the delimiter argument. Your formula should look like `=TEXTJOIN(", ", TRUE, A1:A3)`.
Can I use TEXTJOIN to combine text from non-adjacent cells?
Yes, you can select multiple non-adjacent cells or ranges by separating them with a comma (or semicolon, depending on your region) in the formula, such as `=TEXTJOIN(",", TRUE, A1, B3, C5)`.
