Have you ever needed to combine data from different cells into one? In Google Sheets, the CONCATENATE function allows you to do just that! Whether you want to merge columns or rows of data, this powerful feature can save you time and effort. Let’s dive in and learn how to concatenate in Google Sheets!
The CONCATENATE Function Syntax
Before we get started, let’s take a quick look at the syntax of the CONCATENATE function. The syntax is as follows:
=CONCATENATE(text1, [text2, ...])The texts refer to the information you want to combine. They can be individual cells, ranges, or specified text. Any text beyond the first one is optional.
How to Concatenate in Google Sheets
Now, let’s walk through the steps of concatenating two columns of data. You can also use the CONCATENATE function on rows by creating columns where we suggest creating rows.
1. Create a New Column Next to the Cells You Want to Combine
To create a new column, right-click on a row, column, or cell, then select “+ Insert 1 column” (either to the left or right) from the drop-down menu.
We’ll write our CONCATENATE function in this column, so by the end of this tutorial, your combined results will be displayed in this new column.
2. Write the Function in the First Cell of Your New Column
Select the first cell of your new column and start typing your function, =CONCATENATE(.
Your texts represent the information you want to combine. You can select a specific cell to extract data from that cell, use text between quotation marks (“”), or specify a range of cells. In this case, let’s combine A1 with B1 so that the restaurant’s name is in the same cell as its phone number. Remember to separate your texts with commas:
=CONCATENATE(A1, B1)
In the example above, you’ll notice a preview of the results appearing above the function. You can use it to ensure that you’re concatenating the correct texts. Press Enter to populate your results in the new column.
3. Copy and Paste the Function throughout the Column to Concatenate Other Cells
You can replicate the function throughout your column by copying and pasting the function or by clicking on the cell containing the function and dragging the bottom right corner.
Google Sheets can also suggest the AutoFill function, which will automatically populate your column with your function. You can check the formula by clicking on the “Show formula” hyperlink in the AutoFill suggestion box.
How to Concatenate Entire Columns in Google Sheets
If you want to concatenate multiple texts, you can replace the comma with a colon to specify a range of cells. For example, =CONCATENATE(A1:B4) will yield the same result as =CONCATENATE(A1, B1, A2, B2, A3, B3, A4, B4). When working with ranges, they are joined horizontally and then down the columns.
Let’s try concatenating two individual columns (Column A and Column B) from the above example:
When you start typing the range, Google Sheets will highlight the cells you have included. Close the parentheses and press Enter to combine the columns into a new cell.
How to Add Spaces in CONCATENATE
The cells you concatenate are not automatically formatted. This lack of formatting can impact the readability of your data. You can add spaces between the cells by using an empty string in your formula. To create an empty string, simply add a single space between quotation marks:
=CONCATENATE(A1, " ", B1)
You will need to include an empty string between each text that requires a space.
Concatenating Dates and Times in Google Sheets
If you’re trying to concatenate cells containing dates and times in Google Sheets, you’ll need to take a different approach. There are two ways to combine cells with dates and times, depending on how you have formatted your dates.
1. Format mm/dd/yyyy
You can join this format using a formula. First, select your cell to the right or left of the ones you want to combine. Then, type an equals sign in the formula bar, followed by the cells you want to combine. Instead of separating them with a comma, you’ll need to separate them with a plus sign (+), like this: =A1+B1.
A preview of your resulting cell will appear above the formula bar just like when using the CONCATENATE function.
2. Format mm.dd.yyyy
Concatenating cells with this date format requires using two TEXT functions:
=TEXT(number, format)&TEXT(number, format)
Here, the number is the date or time, and the format is how you want to format the date or time, written inside quotation marks (“”).
For example, to combine a date in A1 with a time in A2, you can write:
=TEXT(A1, "mm.dd.yyyy")&TEXT(A2, "hh:mm")
Here,
A1is your date cell,"mm.dd.yyyy"is your date format,&tells Google Sheets to combine the two text functions,A2is your time cell,"hh:mm"is your time format.
Continue Learning with Crawlan.com
Keep mastering data analysis tools like Google Sheets while earning a certificate for your resume by enrolling in the “Certificate” course at Crawlan.com.
