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() Excel Formula

• 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.

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

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)

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

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

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)

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.” 

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

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

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)

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

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

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

• 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)

• 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”.

• 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 Excel feature. You will understand the output easily instead of writing the full address in a single line.

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

• 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)

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 – 

Recommended Articles

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 – 

• HLOOKUP
• Index Function in Excel
• Wildcard In Excel