Download Excel Workbook
Watch our free training video on How to Concatenate in Microsoft Excel:
CONCATENATE in Excel is a function that allows you to combine text or values from different cells into a single cell. It’s like putting together pieces of information to create a longer piece of text. This can be useful for creating full names, email addresses, labels, or any content that requires merging text or values. You can also add a separator, like a space or a comma, between the combined parts to make the result more readable. In this article, we’ll explore the CONCATENATE function in depth, including its usage with and without a separator.
Download the Excel Workbook below to follow along and understand how to CONCATENATE in Excel –
download excel workbookCONCATENATEinExcel-1.xlsx
Click here to learn about 3 Quick & Easy ways to Concatenate Excel With A Line Break!
SYNTAX
This is the syntax for the CONCATENATE function:
=CONCATENATE(text1, [text2], …)
text1, text2, and so on: These are the text or values you want to concatenate. You can provide multiple arguments separated by commas. These arguments can be cell references, actual text enclosed in double quotation marks, or a combination of both.
Basic Example
In Excel, there are two primary ways to merge data:
Merging Cells
This involves physically joining two or more cells to create a single, larger cell. The content spans across rows and/or columns.
Concatenating Cell Values
Concatenation in Excel is about combining the contents of cells. It’s the process of joining values together. This is often used to combine text from different cells or to insert calculated values into text.
Here’s a visual comparison of these methods:
BONUS: An alternative to the CONCATENATE function, you can use the ampersand (&) operator to achieve the same result. The syntax using the ampersand operator is:
=text1 & [text2] & …
(This approach is highly useful and preferred by Excel users in various situations as using the & symbol is notably quicker than typing out the term “concatenate.”)
Concatenating Text with a Separator: space, comma or other delimiter
In Excel, you can concatenate text from different cells while adding a separator between them. This enhances readability and structure in your combined content. Here’s an example to illustrate this process:
Suppose you have an Excel worksheet with the following data:
In a new cell, let’s say C2, use the following formula:
=CONCATENATE(A2,” “,B2)
This formula concatenates the value in cell A2, adds a space (” “), and then concatenates the value in cell B2. The result will be “Michael Scott” in cell C2.
Alternatively:
=A2&” “&B2
Both of these formulas will yield the same result: “Michael Scott” for cell D2.
(Remember that you can customize the separator by replacing ” ” with any character or text you prefer.)
You may need to join values in a way that includes commas, spaces, various punctuation marks or other characters such as a hyphen or slash. To do this, simply put the desired character in your concatenation formula. Remember to enclose that character in quotation marks, as demonstrated in the following examples.
Concatenating with No Separator
In Excel, you can concatenate text from different cells without using a separator between them. This is useful when you want to combine values directly without any additional characters in between. Here’s an example to demonstrate this:
Let’s use the same data as before:
Now, let’s say you want to create email addresses using the first name and last name from columns A and B. You can achieve this by concatenating the two values directly without a separator:
In a new cell, for instance, let’s say C2, use the following formula:
=CONCATENATE(A2,B2,”@myexcelonline.com”)
Alternatively:
=A2 & B2 & “@myexcelonline.com”
This formula concatenates the value in cell A2, then concatenates the value in cell B2, and finally adds the domain@myexcelonline.com. The result will be “[email protected]” in cell C2.
This approach allows you to merge text values directly, without any added separator, to create specific output like email addresses in this case.
Concatenate Columns
To merge multiple columns, input your concatenation formula in the initial cell, then extend it to other cells by dragging the fill handle (a small square in the lower right corner of the selected cell).
Conclusion
In Excel, CONCATENATE (or &) is a useful tool to merge text, values, numbers, and dates from various cells into one cohesive string. It’s great for making full names, email addresses, or unique identifiers. This understanding empowers you to present your data effectively using Excel’s merging features.
Further Learning:
- Concatenate – Combining Cell Ranges in Excel
- CONCAT Formula in Excel
- 5 Epic Ways to Highlight Duplicates in Excel
Click here to learn more about Concatenate in Excel!
Download the FREE version of Microsoft Excel and start your path on becoming an Excel wizard!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
