Concatenate in Excel is a must when working with data, and this guide covers everything from the Ampersand operator to TextJoin and Concatenate functions.
by Mihir Kamdar / Last Updated:
After reading this guide, you’ll be equipped with the knowledge to:
Download our step-by-step tutorial file now by clicking on the icon 
below and follow along to enhance your Excel skills practically and efficiently!
Merging text in Excel is a common task. Combining text from different cells can be essential for creating cohesive data outputs. One way to achieve this is to merge cells, which helps in organizing and presenting data more effectively. Whether you’re merging names, combining text and numbers or consolidating data from multiple columns, Excel has got you covered. In this guide you will learn several ways to merge text in Excel including formulas and functions like Concatenate, TextJoin and the Ampersand (&) operator.
Concatenation in Excel is the process of combining two or more text strings, cell values, or cell references into a single text string. Concatenation can also be used to merge cells, which involves combining the contents of multiple cells into one. This can be achieved using the CONCATENATE function, or the ampersand (&) operator. Understanding concatenation is essential for preparing complex reports, merging names and addresses, and creating dynamic formulas. For instance, you might need to concatenate cells to create a full address from separate street, city, and state columns. By mastering concatenation, you can streamline your data management tasks and enhance your reporting capabilities.
The Ampersand (&) operator is the simplest way to merge two text cells in Excel. This method is often used to merge cells containing text data.
Syntax: =Cell1 & “ “ & Cell2
Example: To merge first and last names in cells B2 and C2: =B2 & “ “ & C2
The space between the text is added using “ “.
This is fast and works for simple merges.
Steps:
Enter the formula in the desired cell.
Drag down to apply to the range.
To ensure the combined values display correctly across multiple lines, especially when formatting mailing addresses from data in separate columns, enable the ‘Wrap text’ option in the Format Cells dialog.
Concatenate is commonly used to merge text cells in Excel. It can also be used to merge cells containing different types of data. Although replaced by the CONCAT function in newer Excel versions, it’s still popular. In a previous example, we demonstrated how to concatenate text strings by merging mailing addresses with line breaks.
Syntax: =CONCATENATE(Text1, Text2, …)
Example: To merge names in B2 and C2 with a space: =CONCATENATE(B2, “ “, C2)
Steps:
Enter the formula in the desired cell.
Drag down the formula to apply to rows.
Note: This is good for merging text and numbers.
TextJoin is the modern way to merge text in Excel, it allows for more flexibility by handling delimiters and ignoring empty cells. It can also be used to merge cells, providing a seamless way to combine data.
Syntax: =TEXTJOIN(Delimiter, Ignore_empty, Text1, Text2, …)
Example: To merge text in cells B2 and C2 with a space: =TEXTJOIN(“ “, TRUE, B2, C2)
Delimiter: The separator (e.g., “ “ for space).
Ignore_empty: Skips blank cells when TRUE.
Steps:
Enter the formula in the cell.
Apply to the range.
Pro Tip: Use TextJoin to merge data from multiple columns in Excel. TextJoin can also be used to display combined data effectively, ensuring it updates automatically if the source cells change.
To merge text and numbers in Excel use Ampersand or Concatenate. To merge cells, you can use the Merge & Center feature in Excel.
Syntax (Concatenate): =CONCATENATE(Number, “ – “, Text1, “ – “, Text2)
Example: Merge ID, first name and last name in cells A2, B2 and C2: =CONCATENATE(A2, “ – “, B2, “ – “, C2)
Alternatively, with Ampersand: =A2 & “ – “ & B2 & “ – “ & C2
This is good for creating unique identifiers.
Use Ampersand operator (&) or Concatenate function. Use these methods to merge cells. Example: =A1 & “ “ & B1 Or: =CONCATENATE(A1, “ “, B1)
These methods can also be used to merge text from different cells, allowing you to combine data from multiple sources into a single output.
You can’t merge cells and keep all text in one cell. Instead:
Use a formula like =A1 & “ “ & B1 to merge the text into a new cell.
Copy and paste as values if you need to overwrite the original cells.
To ensure the combined values display correctly across multiple lines, enable the ‘Wrap text’ option in the Format Cells dialog.
Excel’s Concatenate function doesn’t preserve formatting. Excel’s Concatenate function doesn’t preserve formatting. However, you can merge cells to combine data from multiple cells into one. But you can use TEXT function to format numbers or dates. Example: =”Amount: “ & TEXT(A1, “$#,##0.00”)
To include a line break in your concatenated text, use the CHAR function to provide the corresponding ASCII code for a line break. For example: =”Address: “ & A1 & CHAR(10) & A2
Merge cells: This method allows you to combine the contents of two cells into one using below methods.
Ampersand: =A1 & “ “ & B1
Concatenate: =CONCATENATE(A1, “ “, B1)
TextJoin: =TEXTJOIN(“ “, TRUE, A1, B1)
These formulas are used to merge text from two cells, effectively combining each cell value with additional text or spaces to create a more meaningful result.
Merge first and last names from two columns using any of the above methods:
Ampersand: =B1 & “ “ & C1
Concatenate: =CONCATENATE(B1, “ “, C1)
TextJoin: =TEXTJOIN(“ “, TRUE, B1, C1)
You can also merge cells to combine first and last names into a single cell.
These methods can also be used to merge text from different cells, allowing you to combine data from multiple separate cells into a single output.
TextJoin in Excel works best: =TEXTJOIN(“, “, TRUE, A1:A5) This merges all text in a range, separated by a comma. This method is particularly useful when you need to merge cells to create a single, combined text output.
These methods can be used to display combined data effectively, ensuring that the combined data will update automatically if the source cells change.
When working with concatenated formulas, you may encounter errors such as #VALUE! or #REF!. These errors can occur due to incorrect syntax, missing quotation marks, or incorrect cell references. To troubleshoot these errors, check the syntax of your formula, ensure that you have used double quotation marks correctly, and verify that your cell references are accurate.
For instance, if you see a #VALUE! error, it might be due to a missing quotation mark or an incorrect cell reference. You can also use the Evaluate Formula tool to step through your formula and identify the source of the error. By addressing these common issues, you can ensure that your concatenated data is accurate and error-free.
Merging text in Excel is a useful skill for data analysis and reporting. Whether you need to concatenate in Excel to merge two text cells, merge columns of text, or merge text and numbers in Excel, Ampersand, Concatenate, and TextJoin make it easy.
Use TextJoin for advanced needs, blank cell ignore, and multiple ranges. Learn this to simplify your data work and reporting.
