In Excel, When you work with data, it’s important to organize it in a way that meets your specific analysis needs. Sometimes, you may encounter situations where you need to split the content of a cell into separate cells or combine data from different columns into a single column. This is where string concatenation becomes a valuable tool. By merging text and data, you can accomplish various tasks, such as combining text with calculated values, merging parts of names and addresses, or formatting dates and times.
How to Use Concatenate in Excel
What is the CONCATENATE Function in Excel?
CONCATENATE means to combine 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 :
Table of Content
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 ” =CONCAT(Selected Cell, “Text”) “
Step 3: Press Enter
CONCATENATE Function
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. 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.
Combine two cells without space
Combining two Cell without a Space in Excel
2. 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.
Combining two Cell with a Space in Excel
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.
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”) “
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. How to combine two cells with a Comma
To combine two Cells with a Dash use CONCATENATE formulae ” =CONCAT(Cell1,”,”,Cell2) “.
2. 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, whch is particularly useful for combining mailing addresses form different columns. Excel’s CONCATENATE function does not directly allow to isnert a line break as you would do with regular characters. You can instead use to CHAR function to provide the corressponding 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 ampresand operator would look like this:
Syntax: text1 & CHAR(10) & text2 & CHAR(10) & text3….
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.
How to Use Concatenate in Excel – FAQs
What is the concatenate function use?
The concatenate Function is like a tool in computer programming that helps put different things together into one big thing. It’s used when you want to mix up stuff from lists, words, or numbers into a single group. People use it for lots of things in programming, like making words longer, working with data or doing math with numbers.
What is the shortcut for CONCATENATE in Excel?
The Shortcut for the ‘CONCATENATE’ function can vary depending on the software or application you’re using.
In Excel, you can use the ‘&’ operator as a shortcut for concatenating cells or text strings. For Example to Concatenate the contents of cells A1 and B1, you can use the formula “=A1 & B1”.
How to CONCATENATE two strings in Excel?
In Microsoft Excel, you can concatenate (join) two strings using the CONCATENATE Function or the ‘&‘ operator.
- Using the Concatenate Function: “=CONCATENATE(text1, text2)”
- Using the & operator : ‘=text 1 & text 2″.
What is the fastest way to concatenate two strings in Excel?
The fastest way to concatenate two strings in Excel is by using the ‘&’ operator. This method is generally preferred over the ‘CONCATENATE’ Function because it is more concise and often performs better in terms of speed and efficiency.