How do I merge two columns in Google Sheets with spaces?

How do I merge two columns in Google Sheets with spaces?

There are a variety of different ways to combine columns in Google Sheets, and I am going to show you five different formulas that you can use to combine multiple columns into one.  Two of these formulas will combine columns horizontally, and three of them will combine columns vertically.

Directly below are quick instructions for combining columns, but also look further below for detailed examples, as well as additional ways of combing columns.

To combine columns horizontally in Google Sheets, follow these steps:

  1. Type =ARRAYFORMULA( to begin your formula for combing columns
  2. Type the address for the first column that you want to combine with, such as A1:A
  3. Type an ampersand (&)
  4. Type the address of the other column that you want to combine with, such as B1:B
  5. Press enter on the keyboard. The full formula will look like this: =ARRAYFORMULA(A1:A&B1:B)

(See detailed instructions below for how to add spaces or special characters between the columns)

To combine columns vertically in Google Sheets, follow these steps:

  1. Type =UNIQUE({ to begin your formulas / array
  2. Type the address for the first column that you want to combine with, such as A1:A
  3. Type a semicolon (;)
  4. Type the address of the other column that you want to combine with, such as B1:B
  5. Type a closing curly bracket ( } )
  6. Press enter on the keyboard. The full formula will look like this: =UNIQUE({A1:A;B1:B})

First I am going to show you how to combine columns in Google sheets horizontally.

If you have two columns that you would like to combine the contents of, where the values of the cells in each row are to be combined together horizontally, then there are a couple simple ways of doing this:

  • The first method (using the ARRAYFORMULA function with the "&" operator / ampersand), will allow you to not only combine/merge two columns horizontally, but will also allow you to separate the contents with specified values/ strings of text, and will also allow you to combine more than two columns if you wish
  • The second method (using the ARRAYFORMULA function with the CONCAT function), will allow you to combine the contents of two or more columns horizontally. The CONCATENATE function is almost identical to CONCAT, and I will mention more about this function below.

Both of these methods can be used to combine individual cells, without having to use the ARRAYFORMULA function.

Using ARRAYFORMULA / & to combine columns

First I will show you the most common and functional way of combining columns horizontally in Google Sheets.

By using the ARRAYFORMULA function with the "&" operator you will be able to combine multiple columns in Google Sheets, and you will also be able to specify values and strings of text that you would like to attach to the column combination.

To combine the data from individual cells, follow these steps:

  1. Type an equals sign, and then type the address for the first cell that you want to combine with, such as A3
  2. Type an ampersand (&)
  3. Type the address of the another cell that you want to combine with, such as B3
  4. Press enter on the keyboard. The full formula will look like this: =A3&B3
  5. Copy / fill down the formula to use it on the entire column, or apply the ARRAYFORMULA function

Here is more information on using the ARRAYFORMULA function, as well as copying formulas.

In this first example we have a list of clothing inventory items in one column, and the clothing size for that item listed in another column… and we want to combine these columns horizontally so that in each row the clothing item and size are displayed as a single text string, in a single column.

We will also put a separating space between the contents that are combined in this example. This is accomplished by using quotation marks with a space between them i.e. (" ") in the array formula (shown below).

The task: Horizontally combine the "item" column with the "size" column, and put a space between the contents… so that the clothing item and size are displayed in a single column.

(Where the contents of the cells in each row are combined into a single text string separated by a space)

The logic: Horizontally combine the contents of the cells in the range A3:A, with the contents of the cells from the range B3:B, and add a space between the text/contents from the combined columns

The formula: The formula below, is entered in the blue cell (D3), for this example

=ARRAYFORMULA(A3:A&" "&B3:B)

(Note: The ARRAYFORMULA function shown above is also perfect for combining first and last name!)

How do I merge two columns in Google Sheets with spaces?

How do I merge two columns in Google Sheets with spaces?

Combining more than 2 columns horizontally

If you want to combine more than 2 columns horizontally in Google Sheets, you can do this with the ARRAYFORMULA function and the "&" operator, which is also called an "ampersand".

For example, if you wanted to combine columns A, B and C, horizontally (with spaces between), then you could use the formula below.

=ARRAYFORMULA(A3:A&" "&B3:B&" "&C3:C)

Using ARRAYFORMULA / CONCAT to merge columns in Google Sheets

In this example we want to achieve the same task as in the first example (merge the "item" column with the "size" column horizontally), however we are going to use a different formula. We will be using the CONCAT function, which is compatible with the ARRAYFORMULA, making it easy to combine an entire column with a single formula.

However, note that you can also use the CONCATENATE function to combine cells and even place spaces / characters between those columns, just like we did with the "&" symbol… but the CONCATENATE function does not work with the ARRAYFORMULA function, and so if you use this to combine columns you will need to use one formula in each cell.

To combine the data from cells with the CONCAT formula, follow these steps:

  1. Type =CONCAT ( to begin your formula
  2. Type the address of the first cell that you want to combine with, such as A2
  3. Type a comma, and then type the address of the next cell that you want to combine with, such as B2
  4. Press enter on the keyboard. The full formula will look like this: =CONCAT (A3,B3)
  5. Copy / fill down the formula to use it on the entire column, or apply the ARRAYFORMULA function

To combine the data from cells by using the CONCATENATE, follow these steps:

  1. Type =CONCATENATE ( to begin your formula
  2. Type the address of the first cell that you want to combine with, such as A5
  3. Type a comma, and then type the address of the next cell that you want to combine with, such as B5
  4. Press enter on the keyboard. The full formula will look like this: =CONCATENATE (A5,B5)
  5. Copy / fill down the formula to use it on the entire column

See the example / instructions above for how to add spaces / special characters between the columns. Again if you want to learn more about expanding formulas, check out these articles on using the ARRAYFORMULA function, as well as copying / filling formulas.

The task: Horizontally combine the column that shows clothing items with the column that shows clothing sizes… so that the clothing item and size are displayed in a single column.

(Where the contents of the cells in each row are combined into a single text string)

The logic: Combine the range A3:B, and the range B3:B into a single column, so that the contents from the cells are horizontally merged into a single text string

The formula: The formula below, is entered in the blue cell (D3), for this example

=ARRAYFORMULA(CONCAT (A3:A,B3:B))

How do I merge two columns in Google Sheets with spaces?

How do I merge two columns in Google Sheets with spaces?

Combine columns in Google Sheets (Vertical)

Now I am going to show you how to combine columns in Google Sheets vertically, or in other words "stack columns".

There are 3 different ways to combine columns in Google Sheets vertically by using formulas, depending on how you would like the formula to operate:

  • The first method (using an array with a semicolon separator), will stack the column ranges that are specified on top of each other exactly as is, including duplicates and empty spaces
  • The second method (using the UNIQUE function with an array), will stack the column ranges that are specified on top of each other… while removing duplicates
  • The third method (using the FILTER function with the LEN function), will stack the column ranges that are specified on top of each other… while removing spaces but keeping duplicates

Using arrays in Google Sheets

In some Google Sheets formulas, you will need to use what is called an "array". An array is similar to what the ARRAYFORMULA does, but it is notated by using curly brackets { } (i.e. braces). In the example below you will see curly brackets used to create arrays. Make sure to include the curly brackets in your formula so that they work properly.

Using an array with a semicolon separator to combine columns

In this example we have two different lists of inventory that we want to combine into one. Let's say that two different employees counted clothing items in separate parts of your store, and that you want to combine the lists that they return to you into one list/column.

By using an array of two different range separated by a semicolon, you can stack the contents of those ranges on top of each other exactly as is, meaning that the resulting range/column will retain any spaces and duplicates that are contained in the specified ranges to be combined. (If you use a comma instead of a semicolon, it will combine the range horizontally instead of vertically)

The task: Combine the individual clothing inventory lists vertically into one long inventory list

The logic: Combine the range A3:A12 and the range B3:B12 vertically into a single column, including any spaces or duplicates

The formula: The formula below, is entered in the blue cell (D3), for this example

={A3:A12;B3:B12}

How do I merge two columns in Google Sheets with spaces?

How do I merge two columns in Google Sheets with spaces?

Combining more than 2 columns vertically with an array

If you want to combine more than 2 columns vertically in Google Sheets, you can do this with an array separated by a semicolon.

For example if you wanted to stack/combine columns A, B, and C vertically, then you could use the formula below.

Remember that this method will keep duplicates, and any empty spaces that fell within the sources ranges.

={A3:A12;B3:B12;C3:C12}

Using UNIQUE with an array to combine columns and remove duplicates

In this example we also have two lists of clothing items found in inventory, and we also want to combine them vertically into a single column… but this time we want to remove any duplicates, and generate a list of unique inventory items (one of each).

By using the same method from the previous example (an array separated by a semicolon), with the UNIQUE function wrapped around it, the list of values that is created will not contain duplicates… and for that same reason the list will only contain up to one empty space in the results.

(In the next example  I'll show you how to remove spaces without removing duplicates)

The task: Combine two lists of clothing items into a single list of unique inventory items… or in other words, make a single list that shows one of each item that is contained in inventory, by combining two lists

The logic: Combine the range A3:A12 and the range B3:B12 vertically into a single column, removing any duplicates

The formula: The formula below, is entered in the blue cell (D3), for this example

=UNIQUE({A3:A12;B3:B12})

How do I merge two columns in Google Sheets with spaces?

How do I merge two columns in Google Sheets with spaces?

Combining more than 2 columns vertically with a unique array

If you want to combine more than 2 columns vertically in Google Sheets, while removing duplicates, you can do this with an array wrapped in the UNIQUE function.

For example if you wanted to stack/combine columns A, B, and C vertically, and to remove any duplicates found in the source range, you could use the formula below.

=UNIQUE({A3:A12;B3:B12;C3:C12})

Using FILTER with LEN to combine columns and remove spaces

I this example we are going to combine the same inventory lists from the previous example, vertically into a single column, but this time we are going to keep any duplicates and remove any empty spaces.

To do this we will create a stacked array as in the previous example, but we will use the FILTER function with the LEN function to remove / filter out empty spaces.

The task: Combine two lists of clothing items into a single list, and show all items found, without no empty spaces

The logic: Combine the contents in range A3:A12 and the contents in range B3:B12 vertically into a single column, and filter out any empty spaces

The formula: The formula below, is entered in the blue cell (D3), for this example

=FILTER({A3:A12;B3:B12}, LEN({A3:A12;B3:B12}))

How do I merge two columns in Google Sheets with spaces?

How do I merge two columns in Google Sheets with spaces?

Combining more than 2 columns vertically with FILTER / LEN

If you want to combine more than 2 columns vertically in Google Sheets, while keeping duplicates but removing any empty spaces, you can do this with the FILTER function and the LEN function.

For example if you wanted to stack/combine columns A, B, and C vertically, and to keep duplicates but remove empty spaces found in the source range, you could use the formula below.

=FILTER({A3:A12;B3:B12;C3:C12}, LEN({A3:A12;B3:B12;C3:C12}))

How to combine columns from separate sheets

You may find situations that require you to combine columns that are from separate tabs in Google Sheets.

This can be done by simply referring to a certain tab name when specifying the ranges in the formula. So where you would normally set a range like "A1:C", when referencing another sheet while combining columns you will specify the tab name, by adding the tab name and an exclamation mark before listing the column and row for the range, like "TabName!A1:C"

However when the tab name has a space in it, you will need to use an apostrophe before and after typing the tab name, like 'Tab Name'!A1:C.

Here is an example of how to combine columns from multiple tabs in Google Sheets, where there are two lists in different tabs, to be combined into a single list on a completely separate tab.

Let's say that you have a list of clothing items on one tab, another list of clothing items on a separate tab, and that you want to display a combined list of unique clothing items that are in inventory, on a completely different tab (meaning that there will be no duplicates).

*Note that you can reference specified tabs with any of the formulas that you learned in this article, but for this example we are using a unique array to demonstrate how to combine columns from different tabs.

The task: Combine the list of clothing items on the tab labeled "List 1", with the list of clothing items on the tab labeled "List 2" and show a combined list of clothing items (without duplicates), on completely separate tab

The logic: Combine the range 'List 1'!A3:A12, with the range 'List 2'!A3:A12, removing any duplicates

The formula: The formula below, is entered in the blue cell (A3), for this example

=UNIQUE({'List 1′!A3:A12;'List 2'!A3:A12})

Here are two different lists of clothing items, which are held on two separate tabs labeled, "List 1" and "List 2"

How do I merge two columns in Google Sheets with spaces?
How do I merge two columns in Google Sheets with spaces?

And here is a combined list of clothing items, where the formula and combined data are held on a separate tab

How do I merge two columns in Google Sheets with spaces?

Pop Quiz: Test your knowledge

Answer the questions below about combining columns in Google Sheets to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.

Classroom downloads:

Combining columns cheat sheet (PDF)

Click here to get your Google Sheets cheat sheet

Click the green "Print" button below to print this entire article.

Question #1

Which of the following formulas will allow you to combine columns vertically? (Choose all that apply)

  1. =ARRAYFORMULA(A1:A&" "&B1:B)
  2. ={C3:C12;D3:D12}
  3. =FILTER({A7:A14;B7:B14}, LEN({A7:A14;B7:B14}))
  4. =ARRAYFORMULA(CONCAT (A5:A,B5:B))
  5. =UNIQUE({A1:A11;B1:B11})

Question #2

Which of the following formulas will put a space between the contents that are horizontally combined?

  1. =ARRAYFORMULA(CONCAT (F3:F,G3:G))
  2. =ARRAYFORMULA(G4:G&" "&H4:H

Question #3

True or False: The following formula will remove all duplicates and empty spaces when combining columns:  ={Y1:Y2;Z1:Z2}

  1. True
  2. False

Question #4

Which of the following formulas will remove duplicates from the combined column?

  1. =FILTER({A17:A100;B17:B100}, LEN({A17:A100;B17:B100}))
  2. =UNIQUE({C5:C15;D5:D15})
  3. ={A2:A20;B2:B20}

Question #5

Which of the following formulas will remove empty spaces from the combined column, but not duplicates?

  1. =FILTER({K3:K12;L3:L12}, LEN({K3:K12;L3:L12}))
  2. =UNIQUE({V3:V12;Z3:Z12})
  3. ={J2:J12;U2:U12}

How do I merge two columns in Google Sheets with spaces?

Answers to the questions above:

Question 1: 2, 3, 5

Question 2: 2

Question 3: 2

Question 4: 2

Question 5: 1

Can I merge two columns in Google Sheets?

Using ARRAYFORMULA / & to combine columns By using the ARRAYFORMULA function with the "&" operator you will be able to combine multiple columns in Google Sheets, and you will also be able to specify values and strings of text that you would like to attach to the column combination.

Can you merge two cells in Google Sheets and keep both data?

You can merge cells using a formula that will bring values together. However, this will cause your original data to disappear, leaving only the new, combined cells. You can avoid losing data in the process, but it requires you to get a Google Sheets add-on.