CONCATENATE Formula in Excel – Table of Contents
Excel CONCATENATE Formula
Concatenate formula in excel simply adds together several cells data into one cell. We can combine multiple cells data into one cell to make the data organized and structured. It can also combine both text & numerical values. In addition, we can also pass our own statement to it.
Take a look at the CONCATENATE formula in excel.
- Text 1: This is nothing but what is the first thing we need to combine together.
- Text 2: Second value we need to combine together with Text 1
Like this, we can combine up to 255 arguments together to have all the 255 cells values in a single cell.
How to Use CONCATENATE Formula in Excel (with Examples)
Below are some of the examples of Concatenate formula in Excel.
One of the typical examples of excel CONCATENATE formula is combining the first name and last name. Consider the below data for an example.
From the above list, we need to combine the first & last name together to have Full Name. Copy the above data to excel sheet to practice along with us.
Step 1: Open excel CONCATENATE function in C2 cell.
Step 2: TEXT 1 is what the first value we need to combine is. In this case, we need to combine the first name first, so select the A2 cell.
Step 3: TEXT 2 is nothing but what the second value we need to combine is. In this case, we need to combine the last name with first name, so select B2 cell.
Step 4: Since we need to add only two names in this case, close the bracket and hit enter.
Oh!!!, hang on we suppose to enter space after the first name isn’t it?
Yes, we need to enter space after the first name. So when the first argument (TEXT 1) selected we need to supply the space with double quotes.
Yes, we got the perfect full name now. Copy and paste the formula to the remaining cells to have a full name for all.
We have seen how excel CONCATENATE formula works. Now we will see how to add our own value to the CONCATENATE function. For example, consider below data.
Using this data we need to frame a sentence, for example, “Santosh Kumar is the employee of the IT Department”.
Step 1: Open excel Concatenate function.
Step 2: Select the first argument as the A2 cell.
Step 3: Second argument is “is the employee of”. So enter this text in double quotes.
Step 4: Now select the B2 cell as the third argument.
Step 5: Final argument is we need to add “Department”. So enter this text.
Step 6: Close the bracket and hit the enter key.
So, we got our own sentence along with cell references to make it a proper sentence.
Note: When you are entering values directly to the formula give spaces after and before the values to avoid separate passing of space character to the formula.
CONCATENATE with Line Breaker
Take the same data from the above example. Assume you want to combine the name & department together but the department should come in a new line like the below one.
Step 1: Open the formula and select the first argument as the A2 cell.
Step 2: Now we need department name to come in the next line, so enter CHAR formula with number 10. CHAR (10) will insert a new line breaker.
Step 3: Now select the B2 cell as the third argument.
Step 4: Well not the exact result we wanted isn’t it??? We need to make the cell as Wrap Text to have a department in the new line.
Alternative to CONCATENATE Formula in Excel
We have an alternative option to concatenate formula in excel. Instead of using this boring formula we can use the symbol ampersand (&) to combine values. For example, take below data as an example.
Step 1: Open equal sing and select A2 cell.
Step 2: Now put the ampersand symbol.
Step 3: Now enter space character in double quotes.
Step 4: Now put one more ampersand and select the B2 cell.
Step 5: Hit enters the key to have a full name.
Wow!! Isn’t it the best alternative you have ever seen in excel?
Things to Remember about CONCATENATE Formula in Excel
- Ampersand symbol is the alternative to CONCATENATE function in excel.
- Excel CONCATENATE formula can accept only one cell at a time.
- CHAR (10) will insert the line breaker.
- Space to be supplied with this code “ “.
This has been a guide to CONCATENATE Formula in Excel. Here we discuss how to use the Concatenate Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
- What is Scroll Lock?
- Examples of AVERAGEIFS Function
- Opposite of Concatenate in Excel
- Nested If Function in Excel
- How to Change Case in Excel?
- How to Freeze Cells in Excel?
- Add Bullet Points in Excel
- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion
- Basic Excel Training
- Advanced Excel Training
- Basic & Advanced VBA Course
- Excel Dashboard Course
- Data Analysis in Excel
- Create VBA Applications