CONCATENATE Function in Excel is a powerful tool for merging text from different cells, streamlining data compilation and reporting. This function simplifies the process of combining names, addresses, or any pieces of information into a single cell, enhancing readability and efficiency. By mastering the CONCATENATE function, you can improve your data organization and presentation, making your Excel spreadsheets more effective and easier to interpret. Embrace this function to take your Excel skills to the next level and ensure your data is presented clearly and concisely.
This Content Covers:
2.1 Concatenate cells without separator
2.2 Concatenate cells with a space, comma or another delimiter
3.1 Concatenate text strings without separator
3.2 Concatenate text strings with a separator
3.3 Concatenate text string and number values
4.1 Concatenate columns without Separator
4.2 Concatenate columns with Separator
1. What is Excel CONCATENATE Function?
Concatenation simply refers to combining or linking two things together. The CONCATENATE function in Microsoft Excel is used to combine the data from two or more cells or columns. In other words, CONCATENATE is a function of Microsoft Excel that concatenates or combines various types of data from two or multiple cells, columns, and rows.
2. How to use CONCATENATE function in Excel to Combine Cells?
Here you will see how to combine two or more cell with and without separator using the CONCATENATE function.
2.1 Concatenate cells without separator
Step 1: Insert the formula given below in cell D2 and press Enter. This formula will combine two cells together.
=CONCATENATE(cell 1,cell 2) or,
Step 2: If you wish to combine three cells together use the following formula.
=CONCATENATE(cell 1,cell 2,cell 3)
2.2 Concatenate cells with a space, comma or another delimiter
Step 1: Use any of the below-given formulas to combine two or more cells with a separator like space, comma, or any other delimiter.
For two cells,
=CONCATENATE(cell 1,” “,cell 2) or,
=CONCATENATE(cell 1,”,”,cell 2) or,
=CONCATENATE(cell 1,”-“,cell 2)
For multiple cells,
=CONCATENATE(cell 1,” “,cell 2,” “,cell 3…..) or,
=CONCATENATE(cell 1,”,”,cell 2,”,“,cell 3…..) or,
=CONCATENATE(cell 1,”-“,cell 2,”-“,cell 3…..)
3. How to use CONCATENATE function in Excel to combine text strings?
The data in A2 and B2 are text strings. If you click on them you will see Text inside the box in the Excel ribbon. We will combine these two text stings with and without separators using the CONCATENATE function.
3.1 Concatenate text strings without separator
Step 1: Select cell D2 and insert the formula shown in the image and click Enter. The text strings will be concatenated. You can combine multiple text stings without a separator using the formula from the previous method about concatenating multiple cells.
3.2 Concatenate text strings with a separator
Step 1: Use any of the formulas from the methods shown before to concatenate these text strings with a separator.
3.3 Concatenate text string and number values
In this picture, we can see column A and C contains number values and column B contains a text string. Just like cells and text strings, we can concatenate text strings and number values using CONCATENATE function.
Step 1: Use the formula from the picture to concatenate text strings with number values without separators.
Step 2: To concatenate text strings with number values with a separator, use the formula from the image below.
4. How to use CONCATENATE function in Excel to Combine Columns?
4.1 Concatenate columns without Separator
Step 1: To combine two or multiple columns without separator use formulas shown before from “Concatenate cells without separator”.
Step 2: Drag the cell down to apply the formula to the entire column to combine two or multiple columns without separator. Here, column A and column B are combined without separator in column D.
4.2 Concatenate columns with Separator
Step 1: Select cell D2 and insert any of the formulas from “Concatenate cells with a space, comma or another delimiter” based on how many columns you want to combine and with which separator.
Step 2: Again, drag the cell down like the previous method and all the columns will be concatenated with the delimiter you inserted.
Application of CONCATENATE Function in Excel
- Merging Names: Combine first and last names from different cells into one cell to create full names, enhancing readability and data management.
- Creating Email Addresses: Use CONCATENATE to merge user names and domain names into complete email addresses, streamlining communication list creation.
- Generating Unique Identifiers: Combine data from multiple cells, such as date, name, and ID, to create unique identifiers or keys for database management.
- Building URLs: Merge website elements stored in separate cells, such as domain, path, and query parameters, to construct full URLs for web analysis or hyperlink creation.
- Formatting Text Output: Combine text strings with data values to create formatted messages or reports, improving the presentation and comprehensibility of data.
- Address Compilation: Concatenate street, city, state, and zip code information from separate columns into one complete address field for mailing or analysis purposes.
For ready-to-use Dashboard Templates:
