CONCATENATE In Excel

What is Concatenate in Excel?

CONCATENATE in Excel is the function to join two or more cell values into one, which may be in the form of characters, strings, or numbers. For example, Excel allows the combination of 255 items, up to 8192 characters, using the CONCATENATE function.

The CONCATENATE in Excel formula is simple and easy to understand and is the substitute for the “&” operator, which is complex. For example, in the following images, we have concatenated “snow” and “ball,” and the result came as “snowball.”

=CONCATENATE(A1,B1)

CONCATENATE Explanation

Concatenate() Excel Formula

CONCATENATE Syntax
  • text1= This is the first cell reference, value, number, or string to be combined. It is the mandatory argument.
  • text2 = This is the second cell reference, value, number, or string to be merged. It is an optional argument and can be added to the user’s requirements.
Flowchart

How To Use CONCATENATE In Excel?

Let us look at some CONCATENATE in Excel examples to understand the formula:

Example #1 (Basic)

The following image shows the list of numbers from 1 to 10. Here, we are required to combine all the cell’s contents in one cell using the CONCATENATE function.

In the table, the data is reflected as below: –

Column A contains Numbers

Column B contains Solution

CONCATENATE in Excel Example 1

Step 1: Concatenate the list of numbers present in column A into cell B1. The following formula is entered in cell B1:

=CONCATENATE(A2,A3,A4,A5,A6,A7,A8,A9,A10,A11)

Example 1.1

Step 2: The solution of the function comes as “12345678910”. The numbers act as a text string and join together.

Example 1.2

Example #2 (With Space)

The following image shows the employees’ names, their year of joining, and the city they belong to, and we are entering all these details in one column to make the data easy to access.

Using the CONCATENATE function, we will combine all the details of all three cells in one cell with space.

In the table below, the data reflected is as follows:

  • Column A contains Names of Employees
  • Column B contains the Year of Joining
  • Column C contains City they belong to
  • Column D combines all two cells with space
CONCATENATE in Excel Example 2

Step 1: We will combine the details of employees present in three different cells into one cell with space in between the text strings. For doing that, you need to enter the following formula in cell D2:

=CONCATENATE(A2,” “,”joined the company in”,” “,B2,” “,”from”,” “,C2)

Example 2.1

Step 2: Enter the formula in cell D2. The result will come as “Akash (space) joined the company in (space) 2019 (space) from (space) Chennai.” 

Example 2.2

Step 3: Press the “Enter” key. Then, drag the formula downwards till cell D7.

 Example 2.3

Example #3 (With Comma)

The following image displays the students’ names and the specialization subjects. With the help of CONCATENATE function, we can combine the name and subject in one cell with a comma.

In the table below, the following data is reflected:

  • Column A contains Names of the students
  • Column B contains specialization Subjects
  • Column C combines all two cells with a comma
CONCATENATE in Excel Example 3

Step 1: Combine the names and subjects of two different cells into one cell with a comma between the text strings. For combining them, you need to enter the following formula in cell C2:

=CONCATENATE(A2,”,”,B2)

Example 3.1

Step 2: Enter the formula in cell C2. The result will come as “John, (comma) Maths.”

Example 3.2

Step 3: Press the “Enter” key. Then, drag the formula downwards till cell C7.

Example 3.3

Example #4 (CONCATENATE Text String with a Line Break, Comma, and Space)

The following image shows people’s first and second names and their postal addresses, such as street, city, postal code, and state.

The CONCATENATE function joins these names and street, city, state, and postal codes in one cell as Full Address with a line break, comma, and space.

In the table, the data is reflected as below:

  • Column A contains the First Name of the people
  • Column B contains the Second Name of the people
  • Column C contains the Street of their home
  • Column D contains City they live in
  • Column E contains State they live in
  • Column F contains the Postal Code of their home
  • Column G combines the whole Address with the full name
CONCATENATE in Excel Example 4
  • Step 1: We combine the full name, second name, street, city, state, postal code, and state of six different cells into one cell with a comma and space between the text strings. We enter the following formula in cell G2:

=CONCATENATE(A2,” “,B2,”,”,” “,C2,”,”,” “,D2,”,”,” “,E2,”,”,” “,F2)

 Example 4.1
  • Step 2: The formula is entered in cell G2, and the result comes as “Ron (space) Weasly, (comma) (space) 34, Davidson Ave., (comma) (space) Chicago, (comma) (space) IL, (comma) (space) 60007”.
 Example 4.2
  • Step 3: Press the “Enter” key. Drag the formula downwards till cell G5.

CONCATENATE function is used with a line break. For example, the full address with the full name is written in three lines but a single cell using the Wrap Text feature. You will understand the output easily instead of writing the full address in a single line.

Example 4.3

Excel CONCATENATE Function vs “&” Operator

CONCATENATE function is easy to use and understand than the “&” operator in Excel. Both combine text strings, numbers, and data in a single cell. However, CONCATENATE has a string limit of 255 characters, while the “&” operator has no string limits of characters.

Important Things To Note About CONCATENATE In Excel

  • The CONCATENATE function will always provide the result as a text string.
  • The numeric values do not require quotes.
  • When combined, the function converts the number to strings, meaning the numeric values merge as a string in the output.
  • The error #VALUE! occurs when an argument of the function is invalid.
  • The error #NAME? appears on missing quotes in the text string argument.
  • If the function does not recognize the array, one cannot enter the range of values.
  • The user must enter individual cell references one by one.
  • The CONCATENATE function has 255 characters limit for concatenated strings.

Frequently Asked Questions (FAQs)

What is CONCATENATE in Excel?


CONCATENATE in Excel formula combines the text, string, or numeric values of different cells into a single cell. In this Excel function, the error such as #NAME? may occur in this function due to incorrect range names or missing quotes around the string.
  
Syntax of CONCATENATE function:

=CONCATENATE(text1,[text2], …)

How to apply to CONCATENATE in Excel?


Follow the below-given step-by-step representation of using CONCATENATE in Excel:

1. Select the cell where you need to enter the formula.
2. In that cell, now, type =CONCATENATE.
3. Select the cells which are to be concatenated.
4. Close the brackets and press the “Enter” key.

=CONCATENATE(text1,[text2], …)

For example, there is an inventory of fruits maintained by a fruit vendor. The following image shows the fruits and their prices. Finally, we will combine all the two cell’s contents in one cell by applying the CONCATENATE function.

In the table below, the following data is reflected:

Column A contains Fruits
Column B contains Prices of fruits
Column C combines all the two cells’ contents

Step 1: Combine each fruit and price of two different cells into one cell. For combining them, you need to enter the following formula in cell C2:

=CONCATENATE(“Price of”,” “,A2,” “,”is ₹”,B2)
CONCATENATE FAQ 1

Step 2: Enter the formula in cell C2. After that, you may obtain the result, “Price of Apple is ₹250.”
CONCATENATE FAQ 2

Step 3: Press the “Enter” key. Then, drag the formula downwards till cell C6.
CONCATENATE FAQ 3

When to use CONCATENATE in Excel?


CONCATENATE in Excel formula is used when the user needs to join the values of two or more cells into one cell. Furthermore –
1. It is used in educational institutions to organize the student’s details.
2. Also, companies may utilize this Excel function to combine their employees’ details.
3. One can also use it in business to merge the stocks, prices, performance, etc., of different commodities.
4. It is applied to perform operations on the given data easily and efficiently. As a result, it reduces time and provides accurate results.

Download Template

This article must be helpful to understand the CONCATENATE in Excel, with its formula and examples. You can download the template here to use it instantly – 

This has been a guide to CONCATENATE in Excel. Here we learn using the CONCATENATE formula with the help of examples and a downloadable excel template. You can learn more from the following articles – 

Reader Interactions

Leave a Reply

Your email address will not be published.