Adrey
Concatenation is basically another word for combining or joining together. You can merge text from various cells into a single cell by using the CONCATENATE function. And often you may need to add a space between those texts. In this article, we have demonstrated some easy ways to add space with the CONCATENATE function in Google Sheets.
A Sample of Practice Spreadsheet
You can download the spreadsheet used to demonstrate examples in this article.
2 Examples to Add Space with CONCATENATE Function in Google Sheets
The CONCATENATE function can be used to merge two cells or multiple, and you can add line breaks wherever you want as well.
1. Adding Space Only
Presume, in the following dataset, we want to merge First name and Last name cells and express them as Full names. Obviously, there should be space between the two names.
Steps:
- Select Cell G5, apply the following formula, then press Enter–
=CONCATENATE(B5," ",C5)The formula CONCATENATE will merge text strings in Cell B5 and C5 and add a space between those texts and that’s why we inserted a space within the quotation marks as the second argument.
- Now, simply Drag-down using the Fill Handle icon as shown in the circled portion.
- And the rest will follow the same command as well.
Read More: How to Concatenate Multiple Cells in Google Sheets (11 Examples)
2. Inserting Space with Line Break
Let’s assume, in the following dataset, we want to express the full name, ID, and location together in one cell by inserting line breaks for each person.
Steps:
- First, choose Cell G5, apply the following formula, and then press Enter–
=CONCATENATE(B5," ",C5,CHAR(10),D5,CHAR(10),E5," ",F5)
Formula Breakdown
- CHAR(10)
The following function adds a line break after completing a particular command.
- CONCATENATE(B5,” “,C5,CHAR(10),D5,CHAR(10),E5,” “,F5)
Firstly, the CONCATENATE function here merges text strings in Cell B5 and Cell C5 together and then adds a line break. After the first line break, it adds a text string from Cell D5. Again adds a line break and then merges text strings in Cell E5 and Cell F5 together.
- Now, Drag-down using the Fill Handle icon as shown in the circled portion.
- And the rest will follow the same as well.
Read More: How to Concatenate Strings with Separator in Google Sheets
Alternatives to Add Space in Google Sheets
You can also use alternative ways to merge texts from different cells into a single cell without the use of CONCATENATE function.
1. Using Ampersand Operator
The Ampersand operator “&” is basically used to merge text strings without the use of any formula. Like, suppose in the following dataset, we want to merge First name and Last name together but we won’t gonna use the CONCATENATE function.
Steps:
- Select Cell G5, type down the following formula, and press Enter–
=B5&" "&C5Here, =B5&” “&C5 returns the same value as =CONCATENATE(B5,” “,C5).
- Now, simply Drag-down like previously using the Fill Handle icon as shown in the circled portion.
- This will merge Cell range B6:B13 and Cell range C6:C13 adding space between them.
Read More: How to Concatenate Strings in Google Sheets (2 Easy Ways)
2. Applying JOIN Function
The JOIN function uses a predefined delimiter to CONCATENATE the components of one or more one-dimensional arrays. Assume, here we want to merge texts from Cell B5 and Cell C5 with a space between them.
Steps:
- Select Cell G5, apply the following formula, and press Enter–
=JOIN(" ",B5,C5)
- After applying the formula in the first cell, simply Drag-down using the Fill Handle icon as shown in the circled portion.
- And the other cells will follow the same formula.
Similar Readings
- How to Concatenate in Google Sheets (6 Suitable Ways)
- How to Concatenate Number and String in Google Sheets
- How to Append Text in Google Sheets (An Easy Guide)
3. Applying ARRAYFORMULA Function
Unfortunately, the CONCATENATE function can’t be used with the ARRAYFORMULA as like SUM, AVERAGE, and other “aggregating” functions, CONCATENATE will devour and process everything enclosed in parentheses. It cannot, therefore, be repeated over an array. Like, in the following dataset, if you apply the combined ARRAYFORMULA and CONCATENATE, it will only work as the CONCATENATE formula in that cell, not for the whole column as we expected.
But obviously, there is a solution for that. The ampersand operator “&” and ARRAYFORMULA will do the same as the CONCATENATE function.
Suppose, in the following dataset, we want to merge text strings from Cell range B5:B13 with Cell range C5:C13 all at once with a space between them and set the same function in a way that will be applied for further input as well.
Steps:
- First, activate Cell G5.
- Then apply the following formula-
=ARRAYFORMULA(B5:B&" "&C5:C)- Finally, just press the Enter button for the output.
- Dragging down, or applying further formulas, nothing is necessary again.
Read More: How to Concatenate Text and Formula in Google Sheets (7 Ways)
Conclusion
All the steps and procedures of adding space with the CONCATENATE function in Google Sheets seemed easy right? Hope, this may help you with your work. Visit our site officewheel.com for more relevant articles. Thank you.
Related Articles
- How to Concatenate Values for IF Condition in Google Sheets
- Concatenate Two Columns in Google Sheets
- How to Get Opposite of Concatenate in Google Sheets (2 Ways)
- Concatenate Double Quotes in Google Sheets (3 Ways)
- Google Sheets QUERY Function to Concatenate Two Columns
- How to Concatenate With Separator in Google Sheets (3 Ways)
- Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)
