1. For example, it can take “2020-02-02” and “0770213”, and combine them into a single value: “2070213”. My favorite way to concatenate in Google Sheets is with the ampersand (&). Just stick the ampersand between any values or cell references you want to glue together.
  2. Joining Multiple Columns Into One Sorted Column in Google Spreadsheets. The core formula is. =transpose (split (arrayformula (concatenate (if (len (A:Z)0,A:Z&';','))),';')) Obviously you'd replace the A:Z to whatever range you want to use.

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:

  • Combine columns into one list with CONCATENATE function
  • Merge columns using NotePad

This step by step tutorial will assist all levels of Excel users to combine multiple columns in Excel and Google Sheets into one column.

Google Sheets Merge Arrays

Combine Columns into One List in Excel with CONCATENATE Function

To combine multiple columns into one we should follow the steps:

  • Insert a new column by selecting entire column D
  • Right-click the mouse and choose Insert

Figure 2. Insert a new column in Excel

  • Select the cell D2 and write the formula: =CONCATENATE(B2,' ',C2)
  • Press enter
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

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.

Merge Excel Columns Using NotePad

Google Sheets Combine Multiple Columns

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:

Combine Columns In Google Sheets
  • Insert new column as explained above
  • Select the columns that you want to combine into one list and click Ctrl+C

Figure 4. Columns to be merged using NotePad

  • Open NotePad: Start, All Programs, Accessories, Notepad
  • Paste values in NotePad using Ctrl+V
Combine two columns in google sheets

Figure 5. Paste column values in NotePad

  • Select the Tab character between columns in NotePad and copy Tab (Ctrl+C or right mouse click and Copy)

Figure 6. Copy Tab character in NotePad

  • Open Replace dialog box with Ctrl+H, paste Tab character in the field Find what and insert space in the field Replace with

Figure 7. Replace dialog box in NotePad

  • Click on the button Replace All to replace the Tab characters with Space and click Cancel to exit the window

Figure 8. Tab characters replaced with space characters

  • Select data in NotePad with Ctrl+A and copy data with Ctrl+C
  • Go back to Excel file and paste data in column D with Ctrl+V

Combine Text In Two Columns Google Sheets

Figure 9. Combine multiple columns into one with Notepad

Combine Multiple Columns in Google Sheets into One Column

Merging Cells Google Sheets

To merge columns in Google Sheets we should follow the steps:

  • Insert a new column by selecting entire column D
  • Right click the mouse and choose Insert 1 left

Figure 10. Insert a new column in Google Sheets

  • In the cell D2 insert the formula: =CONCATENATE(B2,' ',C2)
  • Press enter and drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

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.

Coments are closed

Most Viewed Posts

  • Project Management Prioritization Matrix
  • Retroarch Ps3 Games
  • Combine Data From Multiple Excel Sheets Into One
  • Merge R By Two Columns

Scroll to top