How to Use Concatenate in Excel

Unlock the full potential of your data management skills by mastering how to use CONCAT Function in Excel. This powerful function allows you to seamlessly combine text from multiple cells into one, streamlining your workflow and making your spreadsheets more efficient. Whether you’re preparing complex reports, merging names and addresses, or creating dynamic formulas, understanding how to use the CONCATENATE function in Excel is essential. In this article, we’ll explore the steps to use CONCATENATE effectively, helping you enhance your productivity and data presentation. Get ready to transform your Excel experience with this indispensable tool!

How to Use Concatenate in Excel

What is the CONCATENATE Function in Excel?

CONCATENATE means to combine data 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 a person’s First Name, Middle Name, and Last Name into a single column Full Name. There are two ways in which we can concatenate two or more cell data or texts :

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 the Formula

” =CONCAT(Selected Cell, “Text”) “

Step 3: Press Enter

Use Concatenate Formula >> 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:

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.

Use CONCAT

• Cells Combined

Cells Combined

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.

Use the Formula

• Cells Combined

Cells Combined

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.

Select the Cell and drag to Auto Fill

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”) “

Use Formula ” =CONCATENATE(Cell 1, ” “, Cell 2, ” Text”) “

• Text String Concatenated

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.

How to combine two cells with a Comma

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

How to combine two cells with a Dash

To combine two 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, 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….

Using Char Function

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 up, 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 add this magic. The ‘&‘ symbol is especially cool because it lets you combine as many things as you want.

rishabhchakrabortygfg

Follow

Improve

Article Tags :

• Excel
• Microsoft Office
• News
• ExcelGuide
• MicrosoftExcel
• MSExcel

+2 More