How to Concatenate in Excel (5 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

One of the most important features of Excel is to collect and contain data from various places into a single cell. In other words, to concatenate. Today I will be showing how you can concatenate the values from two or more cells in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Simple Methods to Concatenate in Excel

In the following, I have shared 5 simple methods to Concatenate in Excel.

Here we’ve got a data set with the Employee IDs, Employee Names, and Salaries of some employees of a company named Sunflower Group. Our objective today is to concatenate all the information into a single cell called Merged Information.


1. Using Ampersand (&) Symbol

We can use the Ampersand (&) symbol to concatenate all the information into a single cell. Follow the steps below-

Steps:

  • First, choose a cell (E5) and apply the below formula down-
=B5&", "&C5&", "&D5

Using Ampersand (&) Symbol to Concatenate in Excel

  • Now press ENTER and drag the Fill Handle to fill the formula to the rest of the cells.
  • In summary, we will get the information merged in a single cell. Simple isn’t it?

Using Ampersand (&) Symbol to Concatenate in Excel


2. Using CONCATENATE Function

You can perform a similar task using the CONCATENATE function in Excel. You can do this by inserting all the values that you want to concatenate as the arguments of the function.

Steps:

  • Start with, choosing a cell (E5) and putting the below formula down-
=CONCATENATE(B5,", ",C5,", ",D5)

Using CONCATENATE Function to Concatenate in Excel

  • Then drag the Fill Handle to fill the formula to the rest of the cells.
  • In summary, you will get the output in your hands within a blink of an eye.


3. Excel TEXTJOIN Function to Concatenate

When you have a large range of cells to join, using the Ampersand (&) symbol or the CONCATENATE function may be a bit troublesome. In these cases, you can use the TEXTJOIN function of Excel. Follow the steps below-

Steps:

  • Choose a cell (E5) and apply the formula below-
=TEXTJOIN(", ",TRUE,B5:D5)

Using TEXTJOIN Function to Concatenate in Excel

Explanation of Formula:
  • The first argument of the TEXTJOIN function is the delimiter (By which the texts are separated). We inserted a comma (,) in this case. If you want, you can use anything else as the delimiter. It can be any string.
  • The second argument indicates whether we want to ignore the empty cells within the given range or not. We set it as TRUE. That means, if there are one or more empty cells within our given range, we want to ignore those.
  • The third argument is the range that we want to concatenate. It is B4:D4 for the first employee.
  • Then drag the Fill Handle to copy the formula to the rest of the cells.
  • Finally, we have successfully concatenated the information within a short time.
  • Using TEXTJOIN Function to Concatenate in Excel

    Read More: How to Concatenate Multiple Cells in Excel


    4. Utilizing CONCAT Function to Concatenate

    If you want you can also use the CONCAT function to concatenate text or numeric values in Excel.

    Steps:

    • Presently, choose a cell (E5) and write the below formula down-
    =CONCAT(B5,", ", C5,", ",D5)

    Here, the CONCAT function will join values from the cell (B5:D5) into one cell.

    Utilizing CONCAT Function to Concatenate

    • Gently, press ENTER and drag the “Fill Handle” down to fill the column.
    • In summary, you will get the values concatenated into one single cell.


    5. Using Flash Fill Feature to Concatenate

    Flash fill feature is one of the most tremendous features of Excel which can help you fill multiple columns and rows with your desired values. With the flash fill feature, you can concatenate cells and fill the other rows or columns without applying formulas. Follow the instructions below-

    Steps:

    • Presently, in cell (E5) write your desired text or values.

    Using Flash Fill Feature

    • After that, selecting cells (E5:E13) click the “Flash Fill” option from the “Data” tab.

    Using Flash Fill Feature to concatenate in Excel

    • Finally, you will get to see your desired output in the selected cells. Enjoy!

    how to concatenate in excel

    Read More: How to Concatenate Text in Excel


    Conclusion

    Using these methods, we can concatenate the values from two or more cells into one single cell in Excel. Do you have any questions? Feel free to ask us.


    Concatenate Excel: Knowledge Hub


    << Go Back To Learn Excel

    What is ExcelDemy?

    ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

    Tags:

    Rifat Hassan
    Rifat Hassan

    Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

    We will be happy to hear your thoughts

    Leave a reply

    Advanced Excel Exercises with Solutions PDF

     

     

    ExcelDemy
    Logo