I'd like to combine a range of cells of data so that it comes out with just one text string and a comma + space between each one. I have been successful in using concatenate: =ArrayFormula(concatenate(C3:F&', ')) but there are extra commas that don't need to be there in between some data and a lot of extra commas at the end.
Excel Merge button is not the correct solution to combine columns into one list without losing the data because it keeps only the value from the upper left cell from the selection:
Figure 1. Excel Merge button limitation
There are several ways to merge columns, but the commonly used are:
This step by step tutorial will assist all levels of Excel users to combine multiple columns in Excel and Google Sheets into one column.
To combine multiple columns into one we should follow the steps:
Figure 2. Insert a new column in Excel
Figure 3. Merge Excel columns with CONCATENATE function
Note: we use space under quotations as a delimiter in the formula but we can use any other symbol.
The faster way to merge columns is to use NotePad instead of Excel formulas. As in the previous example, we want to combine multiple columns into one column:
Figure 4. Columns to be merged using NotePad
Figure 5. Paste column values in NotePad
Figure 6. Copy Tab character in NotePad
Figure 7. Replace dialog box in NotePad
Figure 8. Tab characters replaced with space characters
Figure 9. Combine multiple columns into one with Notepad
To merge columns in Google Sheets we should follow the steps:
Figure 10. Insert a new column in Google Sheets
Figure 11. Combine multiple columns into one in Google Sheets
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.