Use of Concatenate in Excel

The CONCAT function combines multiple text strings or cell values into one string, supporting dynamic data merging.

Syntax:

=CONCAT(text1, [text2],)

Parameters:

• text1, text2, ...: The text strings, numbers, or cell references to combine (up to 255 arguments).

Note: In older Excel versions, use CONCATENATE with the same syntax.

How to Combine Data Using the CONCAT Function

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

Step 2: Now type the Formula

" =CONCAT(Selected Cell, "Text") "

Step 3: Press Enter

How to combine two cells in Excel using the CONCAT function

There are two ways to combine two cells in Excel using the CONCAT function:

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

• Cells Combined

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

• Cells Combined

How to Autofill CONCATENATE Column in Excel

Now, suppose we have the "FirstName" and "LastName" 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") "

• Text String Concatenated

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.

1. Combine two cells with a Comma

To combine two Cells with a Dash use CONCATENATE formulae " =CONCAT(Cell1,",",Cell2) ".

2. Combine two cells with a Dash

To combine two Cells with a Dash use CONCATENATE formulae " =CONCAT(Cell1,"-",Cell2) ".

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

How to Concatenate Text String with Line Breaks

Suppose you need to merge text strings with the line breaks, which is particularly useful for combining mailing addresses form different columns. Excel's CONCATENATE function does not directly allow to insert a line break as you would do with regular characters. You can instead use to CHAR function to provide the corresponding 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 Ampersand operator would look like this:

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

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

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.