Using Concatenate in Excel: A Complete Guide

Even expert excel users can sometime face issues with structuring data using the copy and paste function in excel.

This is especially useful when you have tons of disaggregated data.

To help with that, we’ve got the Excel concatenate function for you.

By using concatenate in Excel, you can easily merge data from one column to the other no matter the number of entries.

We teach concatenate on our Microsoft Excel courses as it is so useful when using data imported from other sources.

What Is Concatenate In Excel?

The word concatenate, in essence, means ‘to combine’ or ‘bring two items together.’

When you use the Excel concatenate function, you’re combining the contents of two cells into one cell; values placed in two different cells are merged into one cell.

The data in a cell is referred to as a character on use of a single digit, alphabet, or special character and a ‘text string’ if more than one character is used.

The data can be any piece of text, formula calculated value, or numbers, etc. Syntax of Concatenate function is written as:

 

Where text1 is the text string (text value, no. of digits, a cell reference), you need to combine. (required)text2 can be other items you need to join together. (optional)

text3 can be additional values to be combined. (optional)

It works as demonstrated in the screenshot below:

The natural counterpart to this formula is the TRIM Function, which trims data off a cell instead of combining it.

The & And Concatenate Function In Excel

The concatenate Excel function works more or less the same as the ‘&’ or Ampersand function.

It acts like a substitute for concatenation in Excel and comes in handy since it is relatively quicker and easier to input.

The ‘&’ function lets you combine data in cells without using a complex function.

You can use the ‘&’ function to join together text strings or other data values, and resultantly, you would have the data of two cells combined in one cell.

The ‘&’ operation is used as explained in the screenshot below:

As seen, the ‘&’ function does the same work as concatenate function but is slightly more efficient to use. And the only thing that differentiates between these two functions is the limitation of strings.

Concatenation in Excel only allows input of values up to 255 strings, whereas the ‘&’ function does not impose any maximum limit.

But then again, people in daily life hardly ever need to combine a string as long as 255.

So the limitation of concatenate doesn’t have much influence, and the difference then comes down to the ease, efficiency, and comfort of use. You can choose whichever function suits you best.

Rules of Using the Concatenate Function

Although the Concatenate formula in Excel is simple and easy to apply, it does need a set of rules to work with; otherwise, it shows an error instead of returning values. The rules are listed as:

• When specifying a character or digit, arguments are a must, or the concatenate function will show the ‘#NAME?’ error instead of the output value.
• You can merge up to 255 strings or 1,892 characters in a single concatenate formula.
• Even if a single argument in the concatenate formula is wrong, the result would be a #VALUE error.
• You need to input at least one ‘text’ argument for the concatenate function to process and return value.
• The concatenation Excel function always returns value as a text string even if numbers are being combined.
• Excel concatenate function uses individual cell references since it doesn’t recognize arrays. This means you will have to write =concatenate(C4, C5, C6) instead of =concatenate(C4:C6).
• You don’t need to add quotation marks with numbers in the concatenate formula.
• The concatenate function allows a maximum of 30 arguments only in a single formula.

Using Concatenate

We know the syntax and basic requirements of the concatenate function. Let’s see how it works through a simple demonstration below:

Consider two columns, B and C having certain values.

We use the concatenate function to combine the data in the first two cells.

We apply its formula using =concatenate(B3, C3) as shown in the image below.

To copy the formula to the rest of the cells, simply drag the fill handle (the small green icon at the right bottom of selected cells) till the last value, as demonstrated:

That should clear the how to concatenate in Excel question in your mind.

Also, if you want to delineate on your data set better, try adding borders to it.

Read about adding borders to your worksheet in excel here.

Advanced Uses of the Concatenate Function

So far, we have discussed the primary usage of concatenate in Excel, but its applications don’t end here. While calculating data in Excel, we often need to combine values containing slightly different values than the usual formula.

These include commas, spaces, line breaks, hyphens or ranges, etc. To review these additional arguments, read on.

Concatenating Ranges  

Combining data from several cells at once is time taking and difficult. That’s because the concatenate function in Excel does not allow the usage of arrays, and in every argument, each cell needs to be referenced individually.

If you only have a small range to concatenate, for instance, from B1 to B5, you can enter each cell reference separately using the formula as below:

 

The task is easy with a small number of cells, but if you need to concatenate an extensive range, inputting each reference by hand might not be an option. So to ease you with that, we have jotted down three methods to help concatenate large ranges. These are as follows:

1. Use CTRL key to Select Multiple Cells

To select several cells simultaneously, simply press the CTRL key and select each cell you need to input in the concatenate formula. You can use the CTRL option as:

• Input values in different cells.
• Type the formula and its arguments in a new cell.
• Press the CTRL key and release it only when every value you need has been selected.
• Once released, add in the closing parentheses of the formula and press enter.

2. Merge the Cells

The easiest and quickest method to concatenate ranges in Excel is to merge them using the Merge across function under the Home tab.

Before you combine fields using the Merge function, make sure you turn off the ‘merge all areas in selection’ option to select data in all cells. Next, click OK and that’s it. Your cells have been combined.

3. The Transpose Function

To concatenate a large range having more than 100 cells, we use the Transpose function as other methods might be slow to process such an extensive range.

The result of the Transpose function is an array that is then replaced with separate cell references.

• Select the cell where you want the concatenate function to return value and input the cell references for the Transpose function, say,

 

• Press the F9 key in the formula bar with the cells already selected to change the arguments to calculated values. The formula will then show a number array.
• Remove the braces from both ends of the formula.
• Add =concatenate before the formula and enter the opening and closing parenthesis.
• Once done, press the Enter key.

One thing to bear in mind is that no matter which method you opt for, the result value will be left-aligned, showing that it’s a text string even though each value might be a digit. That is because irrespective of the data entered, concatenate function always only returns a text string.

Inserting Spaces & Commas

Excel does exactly what it is told to, that means it won’t add spaces, commas or any other arguments unless fed into the input.

Say you want to add a single space between your text strings. You will need to add a space argument enclosed in double quotes like this “ “.

So the formula then becomes =concatenate(B3, “ “,C3)

The image below shows the use of arguments in the concatenation Excel formula.

The same rule applies when you enter a comma during concatenation of two cells, the formula changes to =concatenate(B3, “,”, C3), and the string values would then be separated by commas as shown in the image below.

Including Line Breaks

For regular uses, we use delimiters to separate two text strings.

But often, especially when combining mailing addresses, we use line breaks instead of adding punctuation marks, spaces or hyphens, etc.; for the Mac system, you would use carriage return and Windows uses line feed.

As explained before, the concatenate function does not add characters until it is asked to.

So, to add a line break, you need to input a special function called ‘CHAR’ in the concatenate formula in Excel that provides the corresponding ASCII codes. The ASCII code for Windows Line Feed is CHAR 10, whereas, for MAC, the code for Carriage Return is CHAR 13.

Let’s understand the usage of line break with an example below. Say we want to post a mailing address, and all the data, postal code, city name, etc., are in different columns. We concatenate the values using the Ampersand function and input delimiters, including spaces, commas, and the CHAR 10.

Once you input the data, type the formula, add the CHAR 10 and & function. In the image above, cell F2 shows the resultant value of the concatenate formula. Also, as the formula returns the value, turn on the Wrap Text feature so that the result is easy to read.

You can use ASCII codes for delimiters as well, but the easier way to use them is as applied in the example.

Working With Dates

We often need to concatenate digits or dates with a text string.

When you combine two characters of different nature, you would want the nature of your result to be of your choice.

And fortunately, Excel’s concatenate function provides you with this very facility by offering the TEXT function.

The TEXT function enables you to change the nature of the number by formatting it using format codes as per the need of your data. It is useful when you want to type a digit in numbers or combine values of two cells. Its syntax is as:

 

The syntax of the TEXT function consists of two arguments explained as follows:

• Value: Refers to the number that has to be concatenated with the text string. You can even use a cell reference.
• Format_text: Refers to a text string that specifies the style of the numeric value. The formatting is done using format codes.

You can then also format the text to change how it appears.

To ensure your applied formula returns a value in the desired format, you need to use the TEXT function. In other cases, your result might seem broken or out of place. Let’s see an example below to better grasp the concept.

You can use either the concatenate function or the Ampersand function. We need to input date in cell B2 so the formula would be =”Today is” & TEXT(TODAY(), “mm/dd/yy”) as shown in the image below.

Final Verdict

When the need to combine a huge number of cells arises, the Concatenate function is your silver bullet.

While some users find the Concatenate function of excel outdated, it is undoubtedly a quick way to put together values that are otherwise difficult to manage.

Using concatenate in excel is easy and all you need is some practice to become the next Concatenate Expert.

But to make anything work, the input has to be correct. So make sure you enter the correct formula, proper arguments and suitable values.