Reporting & Analysis
28.03.2022
TLDR
Combining columns in Google Sheets can be made easy with four methods: using the Concatenate function to merge columns horizontally or vertically, the Array function for simple two-column combinations, the Combined Array function for entire column merging, and, if you prefer a formula-free approach, using Notepad to combine columns by copying and pasting data. These methods provide flexibility for various column combination needs in Google Sheets.
Combining columns in Google Sheets can be really helpful when you are trying to manage a set of information on a single sheet. It can save time, increase productivity and eventually bring lots of value to the person who uses the feature.
There are many complicated formulas for combining columns in Google Sheets on the internet which may make it seem complicated and inaccessible to some. However, combining columns in Google Sheets can be very easy.
Today, we will discuss four easy ways to combine multiple columns in Google Sheets:
Let’s take a look at each method in detail.
Using Concatenate in Google Sheets
The first method to combine columns in Google Sheets is by using the Concatenate Function.
You can use the formula to combine columns both vertically and horizontally. We'll show you how.
Combine Horizontally
Take the following steps to combine columns horizontally in Google Sheets using the concatenate function.
a. Open a sheet with data, and click right beside the cell where you want to insert a merged column.
b. Right click the cell and tap Insert Column from the drop down menu.
c. Select the column, type the formula “=CONCAT(B3,C3)” and press Enter.
d. Accept the auto fill option Google Sheet provides.
e. Google Sheets will merge all the cells of two different columns in a single one.
Combine Vertically
Take the following steps to combine columns vertically using the concatenate function.
1. Type “=CONCAT(C3,C4)”
2. Press Enter.
3. Both the columns will be combined vertically.
The concatenate function is preferred since it can be used to combine more than two columns at a same time. So, if you have a multiple columns to combine with a single function, you should choose the concatenate function.
Take a look at the steps mentioned below to combine more than two columns in Google Sheets both horizontally and vertically.
Combine Multiple Columns Horizontally
Here's how to combine multiple columns vertically:
1. Left click the cell where you want to combine more than one columns.
2. Type “=CONCATENATE(a3,b3,c3)”
3. Press Enter and allow the auto suggestions.
Combine Multiple Columns Horizontally
Take the following steps to combine multiple columns horizontally.
1. Type “=CONCATENATE(c3,c4,c5,c6)” in the column where you want to make the combination.
2. Press Enter.
3. All the columns will be combined.
Using the ARRAY function
The array function is even easier than the concatenate function. However, the array function is limited to the combination of two columns only.
Take the following steps to combine columns using the array function.
a. Tap the cell of column where you want to make the combination.
b. Type “=ARRAY(A3&B3)” in the formula bar and press Enter.
c. Confirm the auto fill option and Google Sheet will automatically combine the entire column.
Using the combined ARRAY function
This function helps in combining the entire column with a single function.
Take the following steps to utilize this formula:
a. Tap the cell of the column you want to combine.
b. Type “=ARRAYFORMULA(CONCAT(a3:A,b3:b)” and press Enter.
c. Once you do that, your entire row will be combined.
Improve your DTC game. Sign up for weekly tips.
Bonus (no formula needed): Using Notepad
If you can never memorize formulas and prefer simple steps to get the job done, we have a method for you as well.
The steps mentioned below will help you in combining the columns without any formulas. However, you will need to use the Windows app called Notepad (or a similar note app) to get the job done.
a. Open the notepad.
b. Copy the two columns you want to merge in a single one.
c. Paste the data in the notepad.
d. Select the space between the cells and right click to copy it.
e. Type “Ctrl+H” to open the Replace With dialogue box.
f. Left click on the Find What option and type "Ctrl+V" insert the space you have copied in the Replace With option.
g. Select the Replace All option from the right menu.
h. Copy the entire text in the notepad and paste it in a single cell.
i. Both the columns will be combined.
Conclusion
The concatenate feature in Google Sheets is a great way to combine more than one columns. You can use this for combining both horizontally and vertically as well.
If you only need to combine two columns, you can get the job done using the array formula. Take note this is only for a simple combination of two columns.
Finally, if formulas aren't your thing, no worries - Notepad is your trusty no-code solution.
Want more no-code tools to ease your spreadsheets work? Try Airboxr today.
