Pinterest Pixel

Concatenate in Excel – With and Without Separator

CONCATENATE in Excel is a function that allows you to combine text or values from different cells... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Concatenate in Excel - With and Without Separator | MyExcelOnline Concatenate in Excel - With and Without Separator | MyExcelOnline

Concatenate in Excel - With and Without Separator
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], …)

Concatenate in Excel - With and Without Separator

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:

See also  How to Merge Cells in Excel - Step by Step Guide

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:
Concatenate in Excel - With and Without Separator

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] & …

Concatenate in Excel - With and Without Separator

(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:
Concatenate in Excel - With and Without Separator

See also  Excel String Tricks: Unravel the Opposite of Concatenate Function in Excel

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.

Concatenate in Excel - With and Without Separator

Alternatively:

=A2&” “&B2

Both of these formulas will yield the same result: “Michael Scott” for cell D2.

Concatenate in Excel - With and Without Separator

(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.

Concatenate in Excel - With and Without Separator

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:
Concatenate in Excel - With and Without Separator

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:

See also  6 Simple Ways to Merge Cells in Excel

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.

Concatenate in Excel - With and Without Separator

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).

Concatenate in Excel - With and Without Separator

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.

See also  How to Use COUNT for Cost Analysis in Excel

Further Learning:

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!

If you like this Excel tip, please share it
Concatenate in Excel - With and Without Separator | MyExcelOnline Concatenate in Excel - With and Without Separator | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

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.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...