As a data analyzer every time you deal with unstructured data for different purposes which stakeholder needs. Sometimes you have to combine multiple sets of cells and create new data sets based on unstructured data given, and maybe you have to apply comma, space, linebreak, and special characters with that cell combination. Concatenate in google sheets is one of the valuable functions used to combine(join) multiple cells together. To quick implement,
- Open a spreadsheet in Google Sheets.
- Identify the cell that you want to combine.
- The select cell which you need to emit the result.
- Type =CONCATENATE(cell01, other_cells_separate_by_comma)
This article will explain what is concatenated in google sheets? and how to use concatenate formulas with different data operations in google sheets.
Contents
What is concatenate in Google Sheets?
The simple definition is, the operation of a join set of character strings end to end in the given data set, for example, combine “SUB” and “TITLE” as “SUBTITLE” using concatenate in google sheets. Google sheets offer various concatenate methods for different purposes such as CONCAT, CONCATENATE, and LEFT. It will depend on your requirements and complexity.
How to use CONCAT in google sheets?
CONCAT is a basic concatenate in google sheets. It combines two cells equivalent to the `&`(ampersand) operator and appears as the second value after the first. It allows you to combine strings of numeric as one.
Example 01
Let’s take a basic spreadsheet, it has a first name, and last name. Our client need to fill full name row using the first name last name.
Download Sample Sheet
To Import the example datasheet into your spreadsheet,
- Open a spreadsheet in Google Sheets.
- On the top of navigation click File > Import (import datasheet)
- Open a spreadsheet in Google Sheets.
- Select the Appropriate cell you want to appear CONCAT Result in sheets (in example sheet, select D column)
- Type =CONCAT( A2, B2) . A2 column contains the first name and B2 column contains the last name.
- To fill all cells in the column, select D2 and hold + icon to the bottom right then drag to the last row of cells which exists the last cell in the data column.
This is a basic method of CONCAT in google sheets. Make sure that CONCAT does not allow you to combine multiple cells together and it doesn’t have to give space or any special operation. It is equivalent to the `&`(ampersand ) operator. It does not allow to set any space or special operation between selected cells.
To avoid the above limitation you have CONCATENATE in google sheets, it’s an elaborate formula and it allows the appends of multiple cell values with nested formula operation.
How to concatenate multiple cells in google sheets
CONCATENATE in google sheets formula is more advanced rather than CONCAT operation. It allows appending multiple cells and executes the nested operations. For example, if need to give space between cells, you can implement that.
Example 02
Let’s take a basic spreadsheet, it has a title, first name, and last name. Our client needs to fill the full name row using the first name last name with title initialize before the name and make sure need dot “.” after the title and space between the first name and last name.
Download Sample Sheet
To Import the example datasheet into your spreadsheet,
- Open a spreadsheet in Google Sheets.
- On the top of navigation click File > Import (import datasheet)
- Open a spreadsheet in Google Sheets.
- Select the Appropriate cell you want to appear CONCATENATE Result in sheets (in example sheet, select E column).
- Type =CONCATENATE(A2,”. “,B2,” “,C2) . A2 column contains the title then add a dot with space inside double quote next B2 column contains the first name then add space inside double quote next C2 column contains the last name. All specific cell references are separated by commas.
This is the easiest way to combine multiple cell references into one cell inside a column. Also, CONCATENATE formula support executing the nested google sheet formula inside it. For example, if you need to show only the first letter in the first name you have to use the LEFT formula in google Sheets, that formula is used to return substring from the beginning of a specified string.
Type =CONCATENATE(A2,“. “,LEFT(B2,1),“. “,C2) . A2 column contains the title then add dot inside double quote next type LEFT formula and inside type B2 column contains first name and how many strings need to be shown then add space inside double quote next c2 column contains the last name. All specific cell references are separated by a comma.
How to CONCATENATE with line break google sheets?
CONCATENATE in google sheet formula allow to append cell value as horizontally in a cell in the column. You can use google operation as well as special string values as well. But sometimes you have to present concatenate the data and separate each item with line breaks. There have CHAR operations that can be implemented with CONCATENATE formula to present separate each selected cell item with line breaks. Let’s check how to implement with CONCATENATE in google Sheets.
Example 03
Example: Let’s take a basic spreadsheet, it has a St Address, City, State, Post Code. our client needs to combine all the above column data into the Address column with a line break.
Download Sample Sheet
To Import the example datasheet into your spreadsheet,
- Open a spreadsheet in Google Sheets.
- On the top of navigation click File > Import (import datasheet)
CHAR function is the specific formula in google sheets, that can be used for converts an integer number from 1 to 255 according to the current Unicode table. That can be automatically executed with line break in the select cell with append CHAR function in google sheet.
Conclusion
Google sheet is now everywhere, most of the sheet used is recommended google sheets in one top-level cloud tool that resolves all sheet matters. When you work with large data set you always have to concat data for combining multiple cell references into one cell inside a column. CONCATENATE in google sheets is best way to do that.
