When working with Excel spreadsheets, you’ll often need to merge data from multiple cells while maintaining readability by adding spaces between the combined values.
There are multiple methods to concatenate in Excel with spaces, including using the Ampersand symbol, the CONCAT function, and the TEXTJOIN function.
Each method has its unique advantages. Understanding how to utilize them effectively will help you become a more proficient Excel user.
What is Concatenation in Excel?
Concatenation in Excel is the process of joining two or more text strings or values together into a single text string.
This can be particularly useful when you need to combine data from multiple cells or columns, such as first and last names, addresses, or other sets of data that should be displayed together.
But what about spacing? If you had two adjacent cells with “John” in one and “Doe” in the other, you probably want your result to be “John Doe” instead of “JohnDoe”.
In other words, you’ll want to concatenate with a space.
This article shows you how to achieve this with four methods:
the Ampersand operator
the CONCAT function
the CONCATENATE function
the TEXTJOIN function
Open a practice workbook and follow along with the formula examples!
Method 1: The Ampersand Operator With Spaces
The ampersand operator (&) allows you to concatenate text in Excel with spaces.
The advantage of this method is that it is simple and easy to learn. To use it, follow these steps:
Select the cell where you want the concatenated text to appear.
Type an equals sign (=) followed by the single cell reference of the first text you want to combine.
Type an ampersand (&) followed by double quotation marks with a space enclosed (like this: “&” “”).
Type another ampersand (&) and the cell reference of the next text you want to combine.
Press Enter to complete the formula.
Here’s an example to illustrate the process. Suppose you have the text “John” in cell A1 and “Doe” in cell B1.
To concatenate these cells with a space between them, you can use the following formula:
=A1 & ” ” & B1
This formula will return “John Doe” in the cell where it’s entered.
This picture shows the example:
How to Work With Multiple Cells
Remember that you can concatenate more than two cells by repeating the process. For example, if you have text in cells A1, B1, and C1, you can use the formula:
=A1 & ” ” & B1 & ” ” & C1
This method allows you to easily combine two or more cells with spaces, making your Excel worksheets neater and more organized.
Method 2: Using The CONCAT Function
Excel has two functions that are very similar: CONCAT and CONCATENATE. They take a series of text arguments and combine each text value.
CONCAT has replaced the older Excel CONCATENATE function. If you have an older version of Microsoft Excel that doesn’t have CONCAT, skip to the next section.
The basic syntax is: =CONCATENATE(text1, [text2], …)
· The first text argument can be a single cell or a range of cells followed by a comma.
· The optional parameter text2 can be a space within double quotes.
Using our John Doe example with the words in cells A1 and B1, the formula looks like this:
=CONCAT(A1, ” “, B1)
This picture shows the resulting string:
Working With Multiple Cells
If you want to concatenate three cells, the formula looks like this:
=CONCAT(A1, ” “, B1, ” “, C1)
The advantage of using a function instead of the ampersand operator is that typing the & sign into the formula bar can be a little finicky.
If you have several cells to concatenate, this function may be easier to use.
Method 3: Using The CONCATENATE Function
Although the CONCATENATE function is still available in Excel, it has been replaced with the CONCAT function. You should consider using CONCAT going forward.
However, you may have an older version of Excel, so I’ll show you how to use the function here.
The basic syntax is =CONCATENATE(text1, [text2], …)
· The parameter text1 can be a single cell or a range of cells.
· The optional parameter text2 can be a space within quotation marks.
Using our John Doe example, the formula looks like this:
=CONCATENATE(A1, “ “, B1)
Working With Multiple Cells
Here’s an example formula that concatenates three text strings from cells B5, C5, and D5, with spaces between them:
=CONCATENATE(B5, ” “, C5, ” “, D5)
This formula will join the text from the three specified cells and add a space between each cell’s value.
Method 4: Using the TEXTJOIN Function
The TEXTJOIN function allows you to concatenate multiple strings or ranges of cells with a specified delimiter. The basic syntax for the function is as follows:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
delimiter is the character or characters that separate the text items when they are combined. For spaces, use ” “.
ignore_empty is a boolean value (TRUE or FALSE) that determines whether to ignore empty cells in the range. Set this to TRUE to prevent extra delimiters from appearing when there are empty cells.
text1, [text2], … are the text items or ranges to be combined. You can include individual text strings, cell references, or ranges of cells.
Adding Spaces as Delimiters
Here is an example of using the TEXTJOIN function in Excel to combine a range of cells in columns A and B with spaces:
=TEXTJOIN(” “, TRUE, A1:A3, B1:B3)
In this example:
A space character (” “) is used as the delimiter to add spaces.
Empty cells are ignored with the TRUE value in the second parameter.
Range A1:A3 and range B1:B3 are combined with spaces as delimiters.
This formula will result in a single string that combines the values from the specified ranges, with spaces separating all the cells. You can avoid having extra spaces by ignoring empty cells.
This picture shows an example with data. Note that one cell (B2) is empty but the combined cells in the range only produce a single space between the words.
Using the TEXTJOIN function with spaces as delimiters is a powerful way to concatenate strings in Excel, making it an essential tool for your data analysis tasks.
If you want to join text items in a large range of cells, you may want to check the number of distinct values in the results. This video walks you through that task:
5 Tips For Efficient Concatenation
Here are our five best tips to help you concatenate more efficiently with space in Excel:
1. Concatenating a Whole Column
If you want to combine all the cells in column B, use this formula:
=TEXTJOIN(” “, TRUE, B:B)
2. Use CONCAT Instead of CONCATENATE
Some future version of Excel will no longer have the CONCATENATE function. You should use the Excel CONCAT function to avoid having to rework your formulas.
The compatibility feature will help you find deprecated functions.
3. Working With Multiple Rows
If you want the same formula in multiple rows, you can copy it down to multiple rows.
Use the fill handle in the formula cell to autofill to selected cells. You can also double click the lower right corner to achieve the same effect.
4. Use CHAR(10) For Line Breaks
If you want to insert a line break, use CHAR(10). The cell must be formatted to wrap text:
=A1 & CHAR(10) & B1
5. Preserve Data Formatting
When concatenating numbers and dates, the formatting may be lost. To preserve the formatting, use the TEXT function.
For example, if A1 contains a date and B1 contains a number, you can use the following formula to maintain the formatting:
=TEXT(A1, “mm/dd/yyyy”)&” “&TEXT(B1, “#,##0.00”)
Three Common Errors (And Troubleshooting Tips)
As you work with concatenation, you might encounter some issues. In this section, we will discuss common errors and their solutions, so you can get back on track quickly.
1. Concatenate Not Working Due to Cell Formatting
Make sure that the format of the cell with the concatenate function is set to General. To do this:
Select the cell.
Go to the Home ribbon.
Locate the Number section.
Click on the drop-down menu.
Choose General.
2. ‘Show Formulas’ Option is Active
If the ‘Show Formulas’ option is enabled, Excel might not execute the concatenate function.
To fix this:
Go to the Formulas tab.
Toggle off ‘Show Formulas’ if it’s turned on.
The picture below shows the setting turned on (which is now what you want):
3. Provide a Range Instead of Individual Cell References
When using CONCAT or CONCATENATE functions, make sure to reference individual cells rather than a range of cells.
For example, use “=CONCAT(A1, ” “, B1)” instead of “=CONCAT(A1:B1)”.
Our Final Say
In this Excel tutorial, you’ve learned various methods to combine text and other data values with a space in the same cell.
Each approach offers its own benefits. Choosing the right one depends on your specific needs and familiarity with Excel functions. Try them out and find which way works best for you.
