How to Use Concatenate in Excel
Last Updated : 17 Jun, 2024
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 ExcelWhat 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
.webp)
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.
.webp)
Use CONCAT
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
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.
.webp)
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") "
.webp)
Use Formula " =CONCATENATE(Cell 1, " ", Cell 2, " 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.
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) ".
.webp)
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....
.webp)
Using Char FunctionLearn 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 & operatorIn 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.
Similar Reads
How to Concatenate with a Line Break in Excel? Excel is a powerful tool to store, manage, and visualize large amounts of data. This tool is usually used by accounting professionals for the analysis of financial data but can be used by anyone to manage large data. Data is entered into the rows and columns. And then, various formulas and functions
3 min read
How To Concatenate Columns With Alt + Enter In Excel? Use CONCATENATE, one of the text capabilities, to join at least two text strings into one string. Significant: In Excel, Excel Mobile, and Excel for the web, this capability has been supplanted with the CONCAT capability. Assume you have an overview of data in Excel, yet by and by you really want to
2 min read
How to Use the Data Consolidation Feature in Excel? Data consolidation is a feature in Microsoft Excel that allows you to collect data from different worksheets to one single sheet in the same workbook. This feature also allows the user to perform operations like sum, average, max, min, product, etc on the data to be consolidated. Consolidate meaning
4 min read
How to Create a Contingency Table in Excel A contingency table, also known as a crosstab is used to show the relationship between two categorical variables. In Excel, we can make a contingency table using the pivot table function. They are best for summarizing the relationship between categorical variables. A contingency table is just like a
4 min read
Excel VBA Concatenation Operators VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Concatenation means to join two or more data into a single data. There are various ways we
6 min read