How to use the CONCATENATE Function and '&' Operator to Join Text or Numbers or Dates

Hi friends,

In this post I'll discuss how to concatenate text strings, cells, ranges, columns and rows in Excel using the CONCATENATE function and "&" operator step-by-step.

YouTube Video Links :

Concatenate Part 1       Concatenate Part 2       Concatenate Part 3

Download Excel File 

This topic is very interesting as I hope you know CONCATENATE function more or less, but I'll show you some quick methods and magical tricks to use this function very fast.

In practical situations Excel workbook's data is not always structured according to your needs and you may want to organize it, combine data from two or more columns into a single column. Common examples that require concatenation in Excel are joining names and address parts, combining text with a formula-driven value, displaying dates and times in the desired format etc.

So, let's learn how to use CONCATENATE step-by-step in much easier way.

Syntax 

CONCATENATE(text1, [text2], …)

Where text is a text string, cell reference or formula-driven value.

The simplest CONCATENATE formula to combine the values of cells A1 and B1 is as follows:

=CONCATENATE(A1, B1)

Concatenating a text string and cell value

There is no reason for the Excel CONCATENATE function to be limited to only joining cells' values. You can also use it to concatenate various text strings to make the result more meaningful.

For example:

=CONCATENATE(A2," ",B2," ",C2," ",D2," Concatenate Test")

How to use CONCATENATE function

To get the function either you can type =CONCATENATE() or you can click in formula bar to open the argument box as shown above, please drag the slide bar in this little box if you find that the arguments are not enough for your data.

Please notice that we add a space before the word " Concatenate Test" to separate the concatenated text strings.

Naturally, you can add a text string in the beginning or in the middle of your Concatenate formula as well:

A space (" ") is added in between the combined values, so that the result displays as ” E148 Sam T-137 50 Concatenate Test"

Concatenating a text string and a formula

To make the result returned by some formula more understandable for your users, you can concatenate it with a text string that explains what the value actually is.

For example, you can use the following formula to return the current date:

=CONCATENATE(D16," ",TEXT(E16,"dd-mmm-yy"))

Here again we use one function within another i.e., nested function, Test().

The syntax of Text function is =TEXT(value, format_text). 

We gave the reference "E16" to value parameter and "dd-mmm-yy" to format_text, so that we get the date as DD-MMM-YY format.

How to use CONCATENATE function with Date

Things to Remember

a)    In a single CONCATENATE formula, you can concatenate up to 255 strings, a total of 8,192 characters.

b)    The result of the CONCATENATE function is always a text string, even when all of the source values are numbers.

c)    Excel CONCATENATE does not recognize arrays (i'll show you how to use array in CONCATENATE function). Each cell reference must be listed separately. 

For example, 

you should write 

=CONCATENATE(A1, A2, A3) instead of =CONCATENATE(A1:A3).

"&" operator to concatenate strings in Excel

In Microsoft Excel, '&' operator is another way to concatenate cells. This method come in very handy in many scenarios because typing the ampersand (and) sign (&) is much quicker than typing the word "concatenate" .

Similarly to the CONCATENATE function, you can use "&" in Excel to combine different text strings, cell values and results returned by other functions.

For Example

To see the concatenation operator in action, let's re-write the CONCATENATE formulas discussed above:

Concatenate the values in A1 and B1:

=A1&B1 in place of =CONCATENATE(A1, B1)

Difference Between Excel "&" operator and CONCATENATE function...

The only essential difference between CONCATENATE and "&" operator is the 255 strings limit of the Excel CONCATENATE function and no such limitations when using the ampersand. Otherwise, there is no difference between these two concatenation methods, nor is there any speed difference between the CONCATENATE and "&" formulas.

How To Use The TRANSPOSE function to get the range in ARRAY. (Array is nothing but collection of numbers or strings, etc.)

When you need to concatenate a huge range consisting of hundreds of cells, the above method is not fast enough because it requires clicking on each cell. In this case, a tricky & better way is to use the TRANSPOSE function to return an array, and then replace it with individual cell references in one fell swoop.

1.    Select the cell where you want to output the concatenated range.

2.    Enter the TRANSPOSE formula in that cell,       =TRANSPOSE(A1:A10) in this example.

3.    In the formula bar, press F9 to replace the formula with calculated values.

4.    Delete the curly braces that turn a usual Excel formula into an array formula. As a result, you will have all the cells references to be included in your concatenation formula.

Type =CONCATENATE( in front of the cell references in the formula bar, type the closing parenthesis and press Enter.

CONCATENATE and ARRAY

Hope you enjoined this post for further clarification please watch my YouTube video, it will be more clear....