- Merge multiple data cells into a new cell
- Write a function in Google Sheets
- How to write a CONCATENATE function?
- Add spaces to the CONCATENATE function
- Consolidation Limitations
- How to open the CONCATENATE function
Merge multiple data cells into a new cell
In Google Sheets, concatenation usually refers to combining the contents of two or more cells in a worksheet into a third single cell using the CONCATENATE function or its newer version, CONCAT.
Here’s what the CONCATENATE function does and how to use it.
CONCAT and CONCATENATE perform similar functions, but the older version supports ranges and more cells. In CONCAT function, you can only combine two cells, but the formatting is the same.
These instructions use the Google Sheets app for iOS. PC version may vary.
Write a function in Google Sheets
A function in Google Sheets — or in other spreadsheet programs like Microsoft Excel — has three parts, in this order:
The equal sign (=). This tells the program that you are entering a function.
The name of the function you are using. This is usually in capital letters, but it doesn’t have to be. Some examples are SUM, ROUNDUP and PRODUCT.
Set of brackets: (). If your function involves working with a series of numbers in your spreadsheet, they are in parentheses to tell the program what data to use in the formula. Some functions, such as NOW, which return the current date and time, still contain parentheses, but they are empty.
How to write a CONCATENATE function?
CONCATENATE follows the format above, but has some special features. General layout:
= CONCATENATE(line 1, [строка2, …]†
“Rows” refers to specific pieces of data in your spreadsheet. These can be individual cells or their ranges, such as entire rows or columns. The rules for a real function are that you represent at least one data point (or “argument” as Google Sheets calls them), and you must separate each point or range with a comma.
An actual CONCATENATE function might look like this:
= MERGE (A1, B2: B5, A2)
When Spreadsheets executes the function, the result is every item in the cells referenced by the formula, sorted in order.
If your function has a range that contains multiple rows and columns, the contents are displayed in order from left to right and top to bottom, as you would read them.
Add spaces to the CONCATENATE function
Concatenation does not leave spaces between words, but you can embed them in a formula. Wherever you want a space, insert a set of double quotes with a space in between. The above function example with a space between the first two lines looks like this:
=MERGE(A1, “”, B2: B5, A2)
Google Sheets formats the result of the CONCATENATE function as text. If your input already consists of text, this has no effect. But if you’re using numbers, you can’t include the result in math functions like SUM and AVERAGE. This is because math functions ignore text.
How to open the CONCATENATE function
Google Sheets doesn’t use dialog boxes to enter function arguments like Excel does. Instead, it has an auto-suggestion box that pops up when you type a function name in a cell.
Enter the information you want to merge, and then click the cell where you want the merged data to appear.
Enter equal sign † † ) and then type JOIN TOGETHER †
Suggestions appear above the keyboard as you type, so you don’t have to type the whole word.
Click the cells you want to merge in the order you want. You can also drag and select a range. Google Sheets automatically inserts commas to separate rows of data.
The end result are rows in the order you selected them.
To add a space, place the cursor between the two items you want to separate and then enter two double quotes with a space between them. With this element you can add any text you want to the function.
Quotation marks on the default iOS keyboard will not work for this feature. You can use the desktop version or, if possible, add space to the terms you concatenate.
Click Yield or check the box to start the function.
Your merged data will appear in the cell.
Repeat the process for all the cells you want to merge.
If you use absolute values with dollar signs in a formula, you can use autocomplete for specific rows and columns.
Changing the text in any of the cells in the formula also updates the concatenation result.
You can also access CONCATENATE via function button next to the text field on iOS or in the top right corner of the screen on the desktop. The mobile version looks like the letters “fx”, while the desktop version looks like the Greek letter sigma (∑).
CONCATENATE is under the headline Text in the function menu.