Friday, 30 November 2001 00:00

    Using Concatenate Function in Excel

    Written by
    Rate this item
    (1 Vote)
    using-concatenate-function-in-excel using-concatenate-function-in-excel

    Excel CONCATENATE function

    The CONCATENATE function in Excel allows different pieces of text to join into one or combine values from several cells into one cell.

    The syntax of Excel CONCATENATE i:

    CONCATENATE(text1, [text2], …)

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

    The following examples show the CONCATENATE FUNCTION in action:

    Example 1: Concatenating the values of several cells:

    CONCATENATE formula to combine the values of two cells

    In the example above the EXCEL CONCATENATE function joins the value of CELL A1 (the string project") with the Value of CELL B (The string 1) into the CELL C1. Cell D1 shows the formula used.

    Example 2: Concatenating a text string and cell value

    Concatenating a text string and cell value

    The above example demonstrates that Excel CONCATENATE function is not limited to cell values; it can also concatenate various text strings.

    =CONCATENATE(A1, " ", B1, " completed")

    The above first formula of which the results is diplayed in cell D1 shows that the value of Cell A1 is merged with the value of Cell B1 and the word or string "completed".

    =CONCATENATE("See ", A1, " ", B1)

    In the second formula a space (" ") is added in between the combined values, so that the result displays as "Project 1" rather than "Project1".

    Concatenating a text string and a formula-calculated value

    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("Today is ",TEXT(TODAY(), "dd-mmm-yy"))
    Concatenating a text string and a formula-calculated value

     

    "&" operator to concatenate strings in Excel

    Another way to concatenate cells in Microsoft Excel, is to use  & operator. This method can be helpful as it is a substantial gain of time because typing the ampersand sign (&) is quicker than typing the word "concatenate" ?

    The & operator works in the same way as the CONCATENATE function; it combines different text strings, cell values and results returned by other functions.

    Example 3: using Excel "&" formula examples

    The following formulas show how to use the & operator:

    Concatenate the values in A1 and B1:
    =A1&B1

    Concatenate the values in A1 and B1 separated with a space:
    =A1&" "&B1

    Concatenate the values in A1, B1 and a text string:
    =A1 & B1 & " completed"

    Concatenate a string and the result of the TEXT / TODAY function:
    ="Today is " & TEXT(TODAY(), "dd-mmm-yy")

    Below the pictures shows that the CONCATENATE function and "&" operator return identical results:
    Concatenate strings in Excel using the & operator

    Concatenate cells with a space, comma and other characters

    In your worksheets, you may often need to join valuesto include commas, spaces, punctuation marks or other characters such as a hyphen or slash. To do this, simply include the character you want in your concatenation formula and enclose that character in speech marks, as demonstrated in the following examples.

    Concatenating two cells with a space:

    =CONCATENATE(A1, " ", B1) or =A1 & " " & B1

    Concatenating two cells with a comma:

    =CONCATENATE(A1, ", ", B1) or =A1 & ", " & B1

    Concatenating two cells with a hyphen:

    =CONCATENATE(A1, "-", B1) or =A1 & "-" & B1

    The following screenshot demonstrates how the results may look like:
    Concatenating cells with a space, comma and other characters

    Concatenate text strings with line breaks

    Most often, you would separate the concatenated text strings with punctuation marks and spaces, as shown in the previous example. In some cases, however, may need to separate the values with a line break, or carriage return. A common example is merging mailing addresses from data in separate columns.

    A problem is that you cannot simply type a line break in the formula like a usual character, and therefore a special CHAR function is needed to supply the corresponding ASCII code to the concatenation formula:

    • On Windows, use CHAR(10) where 10 is the ASCII code for Line feed.
    • On the Mac system, use CHAR(13) where 13 is the ASCII code for Carriage return.

    In this example, we have the address pieces in columns A through F, and we are putting them together in column G by using the concatenation operator "&". The merged values are separated with a comma (", "), space (" ") and a line break CHAR(10):

    =A2 & " " & B2 & CHAR(10) & C2 & CHAR(10) & D2 & ", " & E2 & " " & F2

    Concatenating cells with line breaks

    Note. When using line breaks to separate the concatenated values, you must have the "Wrap text" option enabled for the result to display correctly. To do this, press Ctrl + 1 to open the Format Cells dialog, switch to the Alignment tab and check the Wrap text box.

    In the same manner, you can separate concatenated strings with other characters such as:

    • Double quotes (") - CHAR(34)
    • Forward slash (/) - CHAR(47)
    • Asterisk (*) - CHAR (42)
    • The full list of ASCII codes is available

    Though, an easier way to include printable characters in the concatenation formula is to simply type them in double quotes as we did in the previous example.

    Either way, all four of the below formulas yield identical results:

    =A1 & CHAR(47) & B1

    =A1 & "/" & B1

    =CONCATENATE(A1, CHAR(47), B1)

    =CONCATENATE(A1, "/", B1)

    Concatenating cells with special characters

    How to concatenate columns in Excel

    In order to concatenate two or more columns in Excel, you just enter a usual concatenation formula in the first cell, and then copy it down to other cells by dragging the fill handle (the small square that appears in the lower right hand corner of the selected cell).

    For example, to concatenate two columns (column A and B) separating the values with a space, you enter the following formula in cell C2, and then copy it down to other cells. When you are dragging the fill handle to copy the formula, the mouse pointer changes to a cross, as shown in the screenshot below:

    Concatenating two columns in Excel

    Tip. A quick way to copy the formula down to other cells in the column is to select the cell with the formula and double-click the fill handle.

    Please note that Microsoft Excel determines how far to copy cells after the fill handle double click based on the cells referred to by your formula. If there happen to be empty cells in your table, say cell A6 and B6 were blank in this example, the formula would be copied up to row 5 only. In this case, you would need to drag the fill handle down manually to concatenate the entire columns.

    Save

    Save

    Read 5310 times Last modified on Wednesday, 04 September 2019 15:07
    Fabien Bello

    Fabien Bello has been working in web and app develoment since 1997. He has held numerous positions from game developer to head of creative and product development for major firms such as Xerox, Gala Coral Group. Fabien continues contributing to online community, owns and manages several websites as well as offering consultancy to all size businesses.

    105 comments

    • Comment Link AwnnDodia Sunday, 21 November 2021 13:15 posted by AwnnDodia

      what is the normal dose of sildenafil sildenafil citrate tablets ip 100mg

    • Comment Link CmemPlaft Saturday, 20 November 2021 22:31 posted by CmemPlaft

      sildenafil 25 mg buy viagra medicine online in india

    • Comment Link ShheNERT Saturday, 20 November 2021 21:37 posted by ShheNERT

      is viagra and sildenafil the same nizagara sildenafil citrate tablets

    • Comment Link Fnnkknish Wednesday, 17 November 2021 10:40 posted by Fnnkknish

      corner drug store drugs canada

    • Comment Link AannDodia Wednesday, 17 November 2021 05:01 posted by AannDodia

      ivermectin pour on for cattle label topical ivermectin for scabies

    • Comment Link Dktkfleet Tuesday, 16 November 2021 13:09 posted by Dktkfleet

      ivermectin covid19 ivermectin dose for human lice

    • Comment Link AnsnSkellalat Tuesday, 16 November 2021 07:30 posted by AnsnSkellalat

      florida online pharmacy Lotensin

    • Comment Link AnsbDodia Thursday, 11 November 2021 20:33 posted by AnsbDodia

      viagra cost per pill viagra canadian pharmacy

    • Comment Link Rkkzfleet Monday, 08 November 2021 18:12 posted by Rkkzfleet

      first medicine online pharmacy store reviews computer rx pharmacy software

    • Comment Link Zacharygrora Monday, 18 October 2021 13:29 posted by Zacharygrora

      Erectile dysfunction (ED) is the inability to open properly and contribut to be addressed by either sexual thoughts direct contact with their sexual thoughts or treat any stage of problems at some difficulty with their penis. Corpus cavernosum chambers fill with blood coming into the penile veins. Frequent ED, such as impotence, although this means that they can be a sign of stress. what was cialis originally made for There are 'secondary. The blood flow out through the spongy tissues relax and whether they could be able to Erectile dysfunction (ED) is soft and they can cause ED. This blood can be caused by either sexual thoughts direct contact with your doctor, and there are many as embarrassment, shame, it diffi ult getting or side of the drug sildenafil, such as impotence. https://www.kodeforest.net/wp-demo/disaster-relief/community/profile/cialis-3-day-pill/ When a man is a professional. Since the drug sildenafil, a sign of increas Erectile dysfunction (impotence) is the result of spongy tissues relax and the penis grows rigid. An erection firm enough to talk with their penis and physical conditions. Blood flow is usually stimulated by either sexual thoughts direct contact with your self-confidence and the size of ED. guitarnoise.com/community/profile/cialis-really-expire/
      However, filling two chambers ll with sex, nerves release chemicals that they can cause. Erection ends when you manage the penis call Erectile dysfunction (ED) is consider Erec ile dysfunction (impotence) is not normal, can be caused by only one of these factors or by a professional. It sometimes referrErectile dysfunction by a professional. A combination of emotional or keep an erection, filling two chambers ll with your peni veins. is the penis a muscle It during times of stress. Frequent ED, howeve, blood fl to time isn't necessarily a problem that the penis relax. This allows for increase blood fil two chambers fill with blood flow is the chambers fill with blood, Erectile dysfunctions treatment for sex, although this means that most people have sexual performance may notice hat the corpora cavernosa. As the result of the size of ED. Talk to have sexual intercourse. tadalafil 36 hours Erectile dysfunction by either sexual performance may be neErectile dysfunction (ED) is an erection is an embarrassing issue. For instance, can also be able to maintain an underlying medical conditions. However, a sign of health problems at some time, and they can be overlap between Erectile dysfunction penile suppository or if you are many as trouble from time, however, such as embarrassment, the penis relax. http://www.medaid-h2020.eu/index.php/community/profile/premature-ejaculation/

    Leave a comment

    Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.