Combine Columns In Google Sheets

Columns

Let’s say you have a spreadsheet with three cells:

  • A2 with “Plymouth
  • A3 with “Road Runner
  • A4 with “1970

Make sure you're signed in to Google, then click File Make a copy. Change the row height of all of the rows to be smaller. Merge cells A1:I1. Insert a row below row 11 and type your name in the first cell. This row contains the name Ben Mathis. Insert a column between columns G and H and type Total Quantity as the column header. I use Google Sheets for the majority of my stock trackers as I can easily pull stock data from Google Finance. The issue with cryptos is that Google Finance ONLY has BTC, ETH, and LTC. So obviously, one would want to just IMPORTHTML from Yahoo Finance or coinmarketcap. Thus, you can easily merge two cells or combine multiple cells in Google sheets. If you are unsatisfied with the merged cells, you can press Ctrl + Z shortcut keys or undo the action from the top ribbon by going to edit, and clicking undo.

How do you join them together to get “Plymouth Road Runner - 1970“? If you don’t know the answer, read on to find out. If you’re an experienced data enthusiast and know the solution, read on anyway. Below, you’ll find a detailed tutorial on how to merge cells in Google Sheets. Here we go!

If you’re looking for a solution to merge sheets and large data sets, check out our Tutorial on How to Combine Data from Multiple Sheets Into One Master Overview

How to merge cells and combine values in Google Sheets
How to combine columns in Google Sheets

Merge cells button: merges cells, not values

Most users know about the Merge cells button on the spreadsheet toolbar.

It allows you to combine cells into one cell. The drawback of this option is that it DOES NOT merge the data. After the merge, you’ll get only the value in the left or top cell. So, our Plymouth task will fail as follows:

Nevertheless, you may find this option useful to visually tweak the table or dashboard you’re building in the spreadsheet.

How Do You Combine Columns In Google Sheets

How to merge cells and combine values in Google Sheets

There are a few functions that you can use to merge cells and concatenate values in one go. Let’s check out which of them can handle our Plymouth task.

Comparison table of functions to merge cells

*Only combined with the TEXT function.

Merge two cells (or more) with CONCAT

The CONCAT function can concatenate two cells or values only.

Syntax

Plymouth task

We need to merge three cells within the Plymouth task, which means the CONCAT is a no go. If you use the following formula

it will return N/A error

However, there is a workaround:

This formula will merge three values without a delimiter. Put in a bit more effort, and here is a formula with delimiters:

Check out the example in the spreadsheet.

Merge several cells with &

The ampersand (&) is a concatenation operator to merge several cells. It also allows you to use different delimiters to separate the concatenated values.

Syntax

Without delimiters

With delimiters

Plymouth task

Let’s apply the following formula to merge the cells for our Plymouth task:

Check out the example in the spreadsheet.

Ampersand did the job! However, this option might be not very convenient if you need to merge a really large number of cells. So, let’s keep going.

Merge multiple data strings with CONCATENATE

Columns

CONCATENATE is the function to combine multiple data strings into one. It’s the CONCAT’s elder brother (or sister) 🙂

Syntax

Without delimiters

With delimiters

Plymouth task

Let’s use the CONCATENATE function to merge cells within the Plymouth task. We’ll apply two formulas, with and without delimiters:

and

Check out the example in the spreadsheet.

Merge multiple cells with JOIN

JOIN is a function to concatenate values in one or several one-dimensional arrays (rows or columns) using a specified delimiter.

Syntax

Plymouth task

At first sight, JOIN does not let you apply different delimiters as CONCATENATE or & do:

However, a bit of creativity and you can handle this. Here is a JOIN formula to specify different delimiters:

Check out the example in the spreadsheet.

Merge text strings with TEXTJOIN

TEXTJOIN is a function to concatenate text values in one or several one-dimensional arrays (rows or columns) using a specified delimiter.

Simply: JOIN for text values only.

Syntax

  • ignore-empty – if false, each empty cell within the array will be added as a delimiter; if true, empty cells will be ignored.

Plymouth task

We have only text values in our Plymouth task, so the TEXTJOIN formula will handle it.

And here is a TEXTJOIN formula to use different delimiters for merged cells:

How ignore-empty works

Let’s increase the array we use within the Plymouth task up to A20 cell and change the delimiter to “-“. Check out the results returned with the ignore-empty parameter set as either true

or false

Check out the examples in the spreadsheet.

BONUS: Merge cells with SUBSTITUTE

You may know SUBSTITUTE – a Google Sheets function to replace existing text strings with new ones. At the same time, it can serve as a way to merge cells (and even columns). Data analysts from Railsware actively use this approach in their workflow.

SUBSTITUTE formula syntax for merging cells:

  • merge_pattern – specify the order of cells and delimiters to merge. For example, A2-A3:A4.

Note: It’s not necessary to use cell indexes in the merge_pattern. You’re free to specify any text, which then will be replaced with a corresponding cell value.

  • text_to_search_for – specify the text from the merge_pattern to be replaced.
  • cell_to_replace_with – specify the cell, which will replace text_to_search_for.
  • [occurrence_number] – an optional parameter to specify the number of text_to_search_for instances to replace. By default, all instances will be replaced.

Plymouth task

Let’s modify the task to merge three cells (A2, A3, A4) using two different delimiters (' ', ' - '). So, the merge_pattern is the following: A2 A3 - A4. Now, we need to assign a cell index to each text_to_search_for instance to implement the replacement. Here is how the formula will look:

The best part here is that you can use any text in the merge_pattern that should be properly separated with the required delimiters. So, examples like

or

will work as well!

Check out the example in the spreadsheet.

Merge cells with date and time

JOIN and TEXTJOIN are the best options to concatenate cells with date and time values. The SUBSTITUTE approach will do the job, as well. Check out the following formulas to merge two cells, A2 with date and A3 with time:

JOIN

TEXTJOIN

SUBSTITUTE

Other options – CONCAT, CONCATENATE and & – will return date-time values as simple numbers. For example, check out the CONCATENATE formula:

This can be fixed if you embed the TEXT function in your CONCATENATE formula as follows:

The same work-around works for CONCAT and & (ampersand) formulas. Check out the examples in the spreadsheet.

Merge data strings with a line break: CHAR(10)

All the functions above concatenate data in line. However, there is an option to make a line break between the data you merge. For this, you’ll need to add CHAR(10) to your &/CONCAT/CONCATENATE/JOIN/TEXTJOIN/SUBSTITUTE formula.

CHAR is a function to insert special characters.

Syntax

  • table_number – the special character number from the current Unicode table in decimal format.

Plymouth task

CHAR(10) inserts a line break. Let’s embed this into our merging cells formulas:

&

CONCAT:

CONCATENATE:

JOIN:

TEXTJOIN:

SUBSTITUTE:

Check out the examples in the spreadsheet.

How to combine columns in Google Sheets

None of the formulas above will merge columns. However, if you combine &, CONCAT or SUBSTITUTE with ARRAYFORMULA, you’ll get the result. Let’s check this out in practice, but first, we need to import some raw data. Coupler.io can pull data from different sources, such as Airtable, Jira (both Cloud and Server), HubSpot, and many more. Check out all available integrations.

To import data, you’ll need to take two steps:

  • Install Coupler.io from the Google Workspace Marketplace (alternatively, you can install it from the Add-ons menu of your spreadsheet)
  • Set up an importer based on the data source you want to pull data from. Check out the Knowledge Base, where you’ll find information about how to set up each of the importers.

We’ve set up a CSV importer, enabled Automatic data refresh at hourly frequency, and pulled a dataset from Quandl, a marketplace for financial, economic and alternative data, as follows:

Read Import CSV Into Google Sheets Without Coding to learn more.

Merge columns horizontally

Our new task is to merge three columns: Date (A:A), New Orders (D:D), and Export Orders (L:L) using “ - ” as a delimiter.

ARRAYFORMULA + CONCAT

CONCAT can merge two cells only, and this pattern remains for columns as well. So, to concatenate three or more columns, we’ll need to use the work-around. Besides, don’t forget that CONCAT returns date-time values as simple numbers, so we’ll need to add TEXT to the formula:

ARRAYFORMULA + &

For ampersand (&) we don’t need to invent any work-arounds. However, the TEXT function for date-time is still needed:

ARRAYFORMULA + SUBSTITUTE

Specify the merge_pattern and add ARRAYFORMULA:

Check out the examples in the spreadsheet.

CONCATENATE, JOIN or TEXTJOIN combined with ARRAYFORMULA don’t work. If you use either of these functions to merge columns, they will return all values from columns merged in a row.

Combine Columns In Google Sheets

Read more about Google Sheets ARRAYFORMULA in our blog post.

Merge columns vertically with an array and semicolon

Vertical merge means that the data from several columns will be appended to each other in a single column. Use an array ({}) with a semicolon (;) as a separator to merge column ranges.

As an example, we’ll use a dataset imported from Airtable. Read our blog post if you need to Connect Airtable to Google Sheets.

Syntax
Formula example

Let’s merge the following data ranges: B1:B5, C1:C5, E1:E5

Hint: If you’re merging column ranges with duplicate values, use the UNIQUE function to remove duplicates. Here is the formula syntax:

Read our tutorial to learn more options to remove duplicates in Google Sheets.

To wrap up: Where will I need to merge cells in real life?

Frankly, for the use cases published on the Coupler.io blog, we’ve mostly needed to split cells in Google Sheets rather than merge them. If the data is being imported with Coupler.io, you can always select the append mode to concatenate data vertically. Learn more about importing modes in Coupler.io. However, you might need to combine cells or columns for your project or daily activities. Feel free to share your case with us in the comments section, and we may include it in this blog post. Good luck with your data!

Back to Blog

Combine Sheets

Combine 2 Columns Into 1 In Google Sheets

Overview

This add-on offers a great opportunity to consolidate data from many spreadsheets into one. Even if your tables differ in formatting, column order, or content, Combine Sheets will perfectly join everything you have in one table using the column headers. You dictate the add-on what sheets to combine, what to do with formatting, and where to put the results on two quick steps.

How Do I Merge Two Columns In Google Sheets

Summary

Google Sheets Combine Multiple Columns

This add-on offers a great opportunity to consolidate data from many spreadsheets into one. Even if your tables differ in formatting, column order, or content, Combine Sheets will perfectly join everything you have in one table using the column headers. You dictate the add-on what sheets to combine, what to do with formatting, and where to put the results on two quick steps.· Combine as many spreadsheets as you need· Add sheets from Google Drive using quick search field· Use entire sheet or adjust the range manually· Transfer the formatting of the original datasets· Review the result in a new spreadsheet, sheet, or a range of your choice· Consider Google Sheets limit for 2 million cellsWhat's more, you can separate combined sheets with empty rows. This way you'll be able to scan through the consolidated data easily and work with it faster. Try it yourself and you won't regret it.What's new?V1.3New features:- One more way to combine data – with a formula! Have the add-on build a formula to join all existing and future records from the chosen sheets together. The formula is always connected to your original files. Hence, all source changes, all new cells, rows and columns will appear in the combined table automatically. Make sure to visit the help page for more details.Improved:- Minor UX improvements.Fixed:- In some cases, the add-on couldn't combine lots of spreadsheets and fired error. Now it offers you to try and process data again or email our customer support service.- Couldn't add files from Drive if they were previously excluded from the add-on tree-view.- Used to add only up to 10 files from Shared Drive.- Minor UI issues.V1.2Improved:- When picking spreadsheets from Drive, files and folders are now sorted A>Z.- The limit for cells has been increased to 5M cells.- UI: elements don't overlap anymore for DPI 200%.Fixed:- The add-on counted the resulting cells incorrectly and exceeded the limit for 5M cells every time the option 'Consider table headers' was not selected.- Didn't work with a one-column range if 'All data' was selected to combine.- Accepted the incorrect range for a custom location for the results.V1.1Fixed:- Wrong selected range if server response time was slow.- Modifying date format after combining.- The message 'The entered range is incorrect' kept showing even after the range was corrected.- Minor UI issues.If you have any questions about consolidating data from many Google Sheets, please post them here: https://www.ablebits.com/docs/howto-combine-google-sheets/We'll be happy to assist!