CONCATENATE Function in Excel: Explained
In this article, you will learn how to use the CONCATENATE formula in Excel.
How does the CONCATENATE function work in Excel?
In Excel, the CONCATENATE function allows you to combine text from multiple cells into one cell.
When do you use the CONCATENATE function in Excel?
The CONCATENATE function is helpful in Excel when combining text from multiple cells into a single cell. This formula can be useful in various situations, such as:
- Creating a list of names or other text data that is spread out across multiple cells
- Combining information from different cells to create a unique identifier or reference number
- Adding signs, such as whitespace or hyphen, between the concatenated texts
How do you use CONCATENATE formula in Excel?
The general syntax of the CONCATENATE function is as follows:
text1: The first item to be combined.
text2…[Optional]: The additional items to be concatenated. The texts are connected by order of the arguments.
Note 1: You need to enclose a text string if you manually enter it in the formula, such as =CONCATENATE(“Apple”, “Banana”).
Note 2: Space is not automatically added between the text strings. You need to include space as one of the arguments in the formula.
Note 3: The CONCATENATE formula can’t take a range as an argument.
For example, if you want to combine a text string of “Hello” and another string of “World”, you can use the formula below:
If you have a cell "B10" with the text "Hello" and a cell "C10" with the text "World", you can use the formula below to combine the text in those two cells into one cell with the text "Hello World".
The following pictures show some sample formulas, including the two examples above, in which two or three text strings are concatenated by the CONCATENATE function with or without symbols, such as space or a hyphen, by manual inputs or cell references.
What is the alternative to the CONCATENATE function in Excel?
It's also possible to use the "&" operator to concatenate texts. For example, the following two formulas return the same results.
The screenshot below shows the examples in which we replace the CONCATENATE functions with an ampersand. As you see in the cases with space or hyphen(s), you can manually combine input text string(s) and cell reference(s) in a formula.
Analyze your live financial data in a snap in Google Sheets
Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows, update numbers in real-time, and save time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. Are you interested in this product but are an Excel user? That’s not a problem at all. You can connect Google Sheets to Excel quickly.
To learn more about LiveFlow, book a demo.