Excel CONCATENATE Function Tutorial

Excel CONCATENATE FunctionThe Excel CONCATENATE Function will put two text ranges together. For example, one range contains a first name and another range contains a last name you can put the two text ranges together in a single cell. Often the CONCATENATE function is not used in Excel in favor the ampersand (&) operator. In this Excel Tutorial I will demonstrate both the CONCATENATE function and ampersand operator to create combined text ranges and also show you how to add other special characters to a text range such as quote characters or line feed characters. The CONCATENATE function can also be used with Excel's LEFT Function and RIGHT Function.

Excel CONCATENATE Function Basics

The Excel CONCATENATE Function will knit together as many text ranges as you want to put together. Each parameter of the CONCATENATE function is a single range and you cannot concatenate a range array (multiple cells). In practice I do not use the CONCATENATE function because I prefer to use the ampersand (&) sign as a quick and easy way to put text together. To be honest typing one ampersand sign is far more efficient than typing the word CONCATENATE. I have not noticed any speed difference between the two methods but I have not rigorously tested this theory.

Excel CONCATENATE Function

Excel CONCATENATE Function – Adding Text Strings

There is no reason that the Excel CONCATENATE Function should be limited to just text ranges, you can also use hard coded text strings, spaces and character functions such as quotes and line feeds (a.k.a. ENTER key).

In this example we are creating a new text range of <LAST_NAME>, <FIRST_NAME>. In this example we have to enter the comma as a constant.

Excel CONCATENATE Fucntion Comma Case Study

Excel CONCATENATE With Special Characters

One frustrating part of creating concatenated text is inserting special characters that have other meanings such as quote characters or line feed characters. The easiest way of inserting special characters is using the Excel CHAR Function. Each character has its own numeric code that ranges from 0 to 255. For example the quote character is CHAR(34) and the life feed (ENTER key) is CHAR(13). In this example we are going to put quote characters around our <FIRST_NAME>&<FIRST_NAME> field.

Excel CONCATENATE Function Quote Case Study

 

,

2 Responses to Excel CONCATENATE Function Tutorial

  1. Bakar June 6, 2013 at 12:52 pm #

    Hi Adrian,
    I think the difference between "CONCATENATE" and the ampersand (&) operator is
    with the "CONCATENATE" you can see the real date(dd/mm/yy) in a text string where as with the (&) you cannot see real date ex> 06-06-2013

    Bakar

    • Adrian Pask June 6, 2013 at 1:23 pm #

      Hmmm, Interesting comment. I had to think about this one a little bit and this is what I think is going on.

      If you had a Excel formula that was

      =CONCATENATE(06-06-2013,06-07-2013)
      then the result would be:
      "06-06-201306-07-2013"

      While it looks like there are two dates there are actually two text strings because you will note that CONCATENATE has "text" as the parameter data type so it reads all the parameters as text (not a date value which is a number). The equivalent formula would be
      =CONCATENATE("06-06-2013","06-07-2013")

      To contrast the CONCATENATE function with the Excel Formula using an ampersand:

      =06-06-2013&06-07-2013

      and the result is

      "-2013-2014"

      What Excel is doing is interpreting the numbers as values with the intermediate result:

      =(06-06-2013)&(06-07-2013)
      =TEXT(-2013,"0")&TEXT(-2014,"0")
      ="-2013"&"-2014"
      ="-2013-2014"

      To get the equivalent of the concatenate function you would have to use text strings in the function:
      ="06-06-2013"&"06-07-2013"
      or if the dates are in ranges A2 and B2 then the Excel Formula would be:
      =TEXT(A2,"mm-dd-yyyy")&TEXT(B2,"mm-dd-yyyy")

Leave a Reply