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