How to Use Concatenate in Excel

Combine data using the CONCAT function

1. Select the cell where you want to put the combined data.
2. Type =CONCAT(Selected Cell, “Text”)
3. Close the formula with a parenthesis and press Enter.

In Excel, when you work with data, it’s important to organize it in a way that meets your specific needs for analysis. Sometimes, you may encounter situations where you need to split the content of a cell into separate cells or combine data from different columns into a single column. This is where string concatenation becomes a valuable tool. By merging text and data together, you can accomplish various tasks, such as combining text with calculated values, merging parts of names and addresses, or formatting dates and times.

What is the CONCATENATE Function in Excel

CONCATENATE means to combine in Excel. The CONCATENATE function is a text function in Microsoft Excel that allows you to combine or join multiple text strings into a single text string. It takes one or more text arguments and combines them in the order you specify. The resulting text string can include text, numbers, cell references, or any combination thereof.

In Excel, CONCATENATE is often used when you need to create a customized text string, such as creating full names by combining first and last names from different cells or constructing complex sentences from various text components.

For example: Joining the First Name, Middle Name, and Last Name of a person into a single column Full Name. There are two ways in which we can concatenate two or more cell data or texts :

1. Using the CONCAT
2. Using the “&” Operator

Note: The keyword used to combine these texts is “CONCATENATE”. However, in the recent 2016, and 2019 versions of Excel, it is now replaced with “CONCAT”.

How to Combine Data Using the CONCAT Function

Step 1: Choose a cell where you want the combined data.

Step 2: Now type =CONCAT(Selected Cell, “Text”)

Step 3: Press Enter

CONCATENATE Function

How to combine two cells in Excel using the CONCAT function

How to Combine two Cell without a Space in Excel

To CONCATENATE values of two different Cells use the simple formulae =CONCATENATE(Cell 1,Cell 2) . This will combine two cells in one without adding a space. Please refer the below image for more clear understanding.

Combine two cells without space

Combining two Cell without a Space in Excel

How to Combine two Cell with a Space in Excel

The CONCATENATE formulae to add a space in between two cells is =CONCATENATE(Cell 1, “ “, Cell 2). Please refer the below image for more clear understanding.

Combining two Cell with a Space in Excel

How to Autofill CONCATENATE Column in Excel

Now, suppose we have the “First Name” and “Last Name” data of 1000 students. In the column “Full Name” for the first record, we can write the above statement to concatenate the two cells. Now, simply drag the “Auto Fill Options” from the first student of the Full Name column to the last. This will automatically fill in the Full names of various students. This is the benefit of CONCATENATE() else one would have to manually enter the record for 1000 students which is cumbersome.

How to CONCATENATE Text String and Cell Value

To combine Text String and Cell Value to make the result more meaningful use formulae =CONCATENATE(Cell 1, ” “, Cell 2, ” Text”)

How to CONCATENATE Cells With Comma, and Other Characters

We can include any symbol, space, or no space between two cell values as per requirements while concatenating.

How to combine two cells with a Comma

To combine tow Cells with a Dash use CONCATENATE formulae =CONCAT(Cell1,”,”,Cell2)

How to combine two cells with a Dash

To combine tow Cells with a Dash use CONCATENATE formulae =CONCAT(Cell1,”-“,Cell2)

Combined Text Using Comma and Dash

How to Combine Text and Function in Excel

You can concatenate the result of a formula with a text string that describes the value, in order to help your users better comprehend the result it returns.

For Example: In the below Image we want to add Text with the TODAY() functions, the concatenate formulae would be =CONCATENATE(“Today’s Date is “,TEXT(TODAY(), “mmmm d, yyyy”))

Joining Text and Today Date Function

How to Concatenate Text String with Line Breaks

Suppose you need to merge text strings with the line breaks, whch is particularly useful for combining mailing addresses form different columns. Excel’s CONCATENATE function does not directly allow to isnert a line break as you would do with regular characters. You can instead use to CHAR function to provide the corressponding ASCII code for a line break.

• On windows, you can use CHAR(10) to represent a Line feed.
• On Mac, you can use CHAR(13) to represent a Carriage return.

For example, you have address components in columns A through C, and you want to combine them in Column D, with each part on a new line. The formula using the ampresand operator would look like this:

Syntax: text1 & CHAR(10) & text2 & CHAR(10) & text3….

Concatenate Text strings with line break

Learn more about “How to Concatenate Text String with Line Breaks”

How to Combine Data with the Ampersand Symbol (&)

The ampersand symbol “&” is an alternate way to concatenate two or more texts in Excel. It is easier and less time-consuming as compared to the above method, as there is no need to type the entire keyword for concatenation.

Step 1: Choose the cell where you want to place the combined data

Step 2: Type an equal sign(=) in that cell

Step 3: Select the first cell that you want to combine

Step 4: Type the ampersand symbol (&)

Step 5: Add a space within the quotation mark(” “)

Step 6: Select Another Cell to be Combined

Step 7: Press the Enter Key

Concatenate using & operator

In this way, using the above two methods, we can perform various concatenations in Excel. The only difference between these two methods is that there is no limit of 255 strings in the case of the “&” operator. We can concatenate as many strings as we want. Both are having the same speed and display the same value in the form of text and show the same error message during faults. It boils down to one choice to use either of the above two methods based on one’s comfort.

Conclusion

To sum it up in simple terms, knowing how to use CONCATENATE( or its modern counterpart CONCAT) in Excel is having a superpower for combining text or numbers in your spreadsheets. It’s handy tool that lets you put different pieces of information together into one neat package. Whether you’re creating full names by joining first and last names, merging addresses, or making customized text strings, CONCATENATE makes it easy.

You can use either CONCATENATE or the ‘&’ symbol to od this ,magic. The ‘&’ symbol is especially cool because it lets you combine as many things as you want.

FAQs

What is the concatenate function use?

The concatenate Function is like a tool in computer programming that helps put different things together into one big thing. It’s used when you want to mix up stuff from lists, words, or numbers into a single group. People use it for lots of things in programming, like making words longer, working with data or doing math with numbers.

What is the shortcut for CONCATENATE in Excel?

The Shortcut for the ‘CONCATENATE’ function can vary depending on the software or application you’re using.

In Excel, you can use the ‘&’ operator as a shortcut for concatenating cells or text strings. For Example to Concatenate the contents of cells A1 and B1, you can use the formula “=A1 & B1”.

How to CONCATENATE two strings in Excel?

In Microsoft Excel, you can concatenate (join) two strings using the CONCATENATE Function or the ‘&’ operator.

Using the Concatenate Function: “=CONCATENATE(text1, text2)”

Using the & operator : ‘=text 1 & text 2″.

What is the fastest way to concatenate two strings in Excel?

The fastest way to concatenate two strings in Excel is by using the ‘&’ operator. This method is generally preferred over the ‘CONCATENATE’ Function because it is more concise and often performs better in terms of speed and efficiency.