5 Concatenate Function Applications in Excel

64
38402
Get 5 applications to concatenate in Excel
Excel Spreadsheets

A CONCATENATE function in a single line of text, to group several strings, forming phrases and sentences, including using cell data from their Excel spreadsheets. You will know details of the function of this function and discover five interesting applications for it.

Basic excel and intermediate online courses

Knowing the structure of the CONCATENAR function

The syntax of the CONCATENATE function, which is able to group a total of up to 255 text strings into one, is very simple and practical. In it, only the first argument is required. The rest will depend on the number of strings that will be grouped. Let's see:

Excel Spreadsheets

= CONCATENATE (TEXT1, [TEXT2], ...)

texto1: This is a required argument and will be the first item to be concatenated.

texto2: Argument optional. From this argument, all others will also be optional, observing the above mentioned limit.

All the items in the function must be separated by semicolons (;). Another way to get grouping of text strings is to replace the CONCATENATE function with the & operator. If we were to apply the function in the formulas A1 and B1, we could do it in the following ways:

= CONCATENATE (A1; B1)

= A1 & B1

Examples of CONCATENAR function applications in Excel

Now that we know its characteristics, we will know some possibilities of applying the CONCATENAR function:

Grouping first and last name

In our first example, we will group names and surnames into a table using the function.

Get 5 applications to concatenate in Excel

The first argument will logically be the first name (which in our example is in cell D5). The next item has an important aspect: since we need to leave a space between the first and last name, the second argument will be exactly the white space (""). The third item will be the E5 cell, which is equivalent to the last name. Check out:

Get 5 applications to concatenate in Excel

Thus, closing the parentheses will have the function structure ready. Just drag the other cells below and we'll have the first and last names in the same text string.

Get 5 applications to concatenate in Excel

Basic excel and intermediate online courses

Concatenating pre-defined cell and text data

Another possible application is the use, in the same formula, of arguments extracted from cells and pre-defined texts. In this case, we will use the text string of the D5 cell in the first argument. In the second item, we will define the display of a dash (separated by empty spaces) and in the last argument the country (Brazil). Here's how the final result is:

Get 5 applications to concatenate in Excel

Working with Date Series

There is also the possibility to apply the CONCATENAR function with series of dates. To be successful, you must add the TEXT function, thus creating a special formatting to prevent the Excel understand the dates as mathematical formulas. The syntax would look like this:

= CONNECT (TEXT (D5; "DD / MM / YYYY"); "-"; TEXT (E5; "DD / MM / YYYY"))

Check out our example:

Get 5 applications to concatenate in Excel

Get 5 applications to concatenate in Excel

Combining the CONCATENATE function with other functions

Thanks to its versatility, Excel allows the user to extract the maximum from their worksheets combining functions. In the next example, we'll show you how to apply the CONCATENATE and AVERAGE functions together.

Get 5 applications to concatenate in Excel

Note that we first use the CONCATENATE function. In the first argument, we put the text "Final Average" and in the second the equality sign surrounded by empty spaces (":"). The novelty here is in the third item, in which we use the AVERAGE function to obtain the final result of the students. Here's how it went:

Get 5 applications to concatenate in Excel

Applying the & operator

Finally, we'll show you how to apply the "&" operator instead of the CONCATENATE function. Using our previous example, we will delete the function name and the parentheses, replacing the semicolon with the operator. Since we are dealing with two functions, the parentheses for the mean remain. It will look like this:

Get 5 applications to concatenate in Excel

You have learned in this article the structure of the CONCATENAR function and some of its possible applications. Take advantage of these features to boost your spreadsheets and get even more satisfying results. Share your experience with us in the comments and get to know our excel online courses.

Basic excel and intermediate online courses

Excel Spreadsheets

64 COMMENTS

  1. Good Morning!
    Rafael, I have a csv file that I use to generate the items, quantity and description of my online inventory for my clients. I wanted to use concatenate on columns D and E, is there a way to copy the formula to 2 thousand rows?
    I thank you.

  2. Hi Pablo, you can use CONT.SES and as a criterion, define the actions you want to count

  3. Good afternoon, how do I count multiple actions contained in the same column? for example: in the same column there are actions such as: “referral to cras'; referral to hospital ”… .etc
    I just need to count the shares, is it possible?

  4. Hi José, just use the conditional SE with CONCATENAR. Assuming that the result of Juliana (Final) is in cell G2, then in H2 you could write = SE (G2 = "Final"; "Juliana will" & G2; "Juliana is" & G2) - in that case I imagine if it does not is in the end is approved or disapproved and the field there will do just that

  5. Hello how do I use concatenate and function if at the same time?
    Name Last Name Note 1 Note 2 Note 3 Average Situation Report
    Juliana Souza 40 50 80 50 Final I WANTED IT TO BE
    SO MORE
    I DID NOT MAKE IT

    Juliana will do Final
    I wanted to know the formula to reach this result

  6. Next, I am concatenating the value of 3 cells that correspond respectively year / month / day, using the concatenate function ex: concantenar (cel1; "/"; cel2; "/"; cel3). works detail that when the day cell has only 1 type the concatenation does not consider the zero to the left, even the source field is correct ex: 06
    thus:
    6/10/2018

  7. I have a spreadsheet that has this formula = CONCATENATE ("January", "Revenue and Expenses"! E1) and the name that appears in the cell is JANUARY 2016, how do I change to get 2018?

  8. Hi Ricardo, I really could not understand exactly what you need to do

  9. Thank you Rafael! Okay, but I do not think I explained all the lines between my need. Let us suppose that we have diverse events spread over time and that not all have the same cause but that we need to concatenate for a specific cause. Ex.:

    Date Event Start End Cause
    29 / 08 E1 00: 00 01: 00 X
    29 / 08 E2 23: 40 01: 20 X
    29 / 08 E3 00: 10 01: 10 X
    30 / 08 E4 00: 00 01: 20 Y
    30 / 08 E5 00: 00 01: 20 Y
    31 / 08 E6 23: 50 01: 00 X
    31 / 08 E7 00: 05 00: 50 X
    31 / 08 E8 00: 10 01: 00 Y

    In this case the result, if we want to know only the concatenation of cause X, would be:

    Intersection
    Start date End cause
    29 / 08 00: 00 01: 00 X
    31 / 08 00: 05 00: 50 X

    That is, if only one event with cause X happens in isolation, this event would be the result of concatenation. If other X-cause events happen in the same time interval while still having other # X cause events, concatenation would be the common interval between these X-cause events.

    This is a challenge that I have not yet been able to overcome.

  10. Hi Yuri, I think it will be necessary to use the TEXT FOR COLUMNS functionality to separate the values ​​and products and then CONCATENATE and SUM to merge everything into a single cell

  11. how to concatenate a table? for example (C4: D12)
    I have a thousand lines
    column A has several CNPJs (more than one row may appear, depends on the contracted product quantity of column B);
    column B has product1, product2, product3, monthly
    Column C has the values ​​for each: 20, 30, 40, 50.
    The challenge is to bring in a single line
    Company's CNPJ COLUMN
    COLUMN B concatenate contracted products + value of each
    COLUMN C total sum of contracted values
    any suggestion?

  12. Good afternoon,
    Please can you help me? I have the data below in the excel worksheet I can use some formula to leave as in example no2?
    Example No.1: BDM010 (A2) R $ 3.79 (B2) 360pcs (C2)

    Example No.2: BDM010
    R$ 3,79
    360s
    Thankfully,
    Evaneide

  13. Hi Max, you can use the same CONCATENAR, but when you do, the date will deform, so you will need to adjust the formatting using the YEAR, MONTH and DAY functions so that everything is correct

  14. Very good day !! Rafael

    I've been banging head for several days to formulate or even figure out which function can put in and only in a "cell" in Excel the following double information:

    Date

    Example: 22 / 07 / 2018 to 22 / 07 / 2018
    In my invoice report I have the following information:
    Example: Launch Period: 22 / 07 / 2018 to 22 / 07 / 2018

    Sds Maxsoell Nunes
    City: Marabá - Pará
    Date: 22 / 07 / 2018
    Hour / Time: 08: 39hs

  15. Hi David, in that case it is necessary to put a rounding function. The result would be something like = CONCATENATE (A1; 2); A3; ROUNDNESS (A2; 2)

  16. Hi, Rafael. You would need to generate a table in which the values ​​of the mean and the standard deviation separated by the "±" sign appear concatenated.
    I have succeeded in doing this by applying the following: = CONCATENATE (A1; A3; A2) (assuming A1 a cell with the result of the average function, A2, deviation and A3 a cell with the sign "±") but generates all decimals generated when calculating the mean and the deviation, and I need only 2 decimals to appear for each.

    Thank you very much in advance

  17. Hi Mark, in this case you need a code that copies and paste the values

  18. Hi Rogério, in this case it seems to me that you can use the INDIRECT function to get the value of the D1 cell as a reference. something like = INDIRECT (CONCATENATE ("Coupons! I"; D1)

  19. How to concatenate the contents of a cell from another folder on the same worksheet?
    Example:
    Folder: Coupons
    Cell: I206 (but 206 is what I want to concatenate in the other folder)

    Folder: Publish
    Cellphone: E1
    Being that in the D1 cell I will type the cell line to concatenate. In the example, 206.

    Understand: My Coupons folder has multiple rows. And what should I publish (in the PUBLISH folder) has the default text + contents of the I206 phone. But after posting on social networks, 206 passes to 207 ... 208 ... 209 ... and so on until I publish everything from the folder "CUPONS"
    So the idea is that I just type the line and Excel concatenates!

  20. How do I concatenate two columns, then need to delete the two columns and not lose the concatenated result in the concatenated results column?

  21. How to concatenate a tab with 15.000 values ​​without selecting cell by cell?

  22. Hi Mariane, you can copy and paste VALUES - so you will not lose what you have already written, but you will lose the formula that was written and new values ​​will not be entered

  23. Hello, I used the concatenate function in a spreadsheet where I insert information to each contact maintained with the client, before I inserted the information column by column, now they are already grouped, thanks to the function.
    My question is, would it be possible for me to copy this grouped information? To maintain it this way without the formula?
    I would like to delete all the other columns and continue inserting the information in the first standard column, but obviously if I delete, with the formula inserted, all content is lost as well.
    How can I do it?

  24. Hello, is there any difference other than practicality when using & instead of concatenating? Is there any situation where & will be invalid and should I use Concatenate?

  25. You can pick up the highest start value and the lowest end value to arrive at those values ​​that you listed in intersection - the HIGHEST AND LOWER or MAXIMUM and MINIMUM functions would solve this

  26. Guys, good afternoon.

    I'm having difficulty generating a spreadsheet that shows the time interval of intersection between several events occurring at different times. Example below:

    End Event Start
    E1 00: 00 01: 00
    E2 23: 40 01: 20
    E3 00: 10 01: 10
    E4 00: 00 01: 20
    00 Intersection: 10 01: 00

    where the result would be the intersection line that would show the common time interval for all events.

    Would anyone have an idea?

  27. Hi Roberval, instead of using the text function, try to do the same via cell number formatting - INITIAL GUIDE, SOURCE group> choose persnalized format

  28. Hello!
    I need to use the text function as follows:
    In cell A1: 0200201230
    In cell B1: = TEXT (A1; "00.000.0000-0")
    Expected result in cell B1 = 02.002.0123-0, but I can not get the text function to display the "." Character in the desired order, I've already tried = TEXT (A1, "00", "000", "0000-0") , but it did not work.
    Does anyone know how I can display the dot character in cell formatting using the text function?

  29. Hi, Danielle, you have. Let's say in parts, assuming you have a date written in cell A1 and an auxiliary table in B1: C12 with the numbers of months in column B (1, 2, 3, etc) and their names in column C (January, February , etc). In this case, the formula would be:

    = DAY (A1) & "PROCV (A1; B1: C12; 2; FALSE)" & "ANX (A1)

  30. I also wanted that answer, my God! Hahaha ... It's easier to type each number than to apply the cell. Because we have to include, for example, 30 cell, one by one, and before adding the next cell, type ",".
    Example:
    A2; ","; A3; ","; A4 ...
    What I do is:
    Copy and paste this text:; ",";
    I click on the cell A2 and ctrlV, and I repeat until the end ...
    If anyone knows a simpler way, please let me know !!!

  31. Hello
    I loved your explanation of Concatenar, congratulations !!
    Can you help me? I saw that to concatenate numbers you put the formatting first after the DD / MM / YYYY model. Is there any way the date stays in this template: "12 March 2018"? I do not know how to express this in the formula.

  32. Yes, it is possible, but you need to pay attention to the formatting of them that you will lose by doing this

  33. Can I concatenate two cells that have only numbers? I'm trying to do this, but when I hit enter, only the formula

  34. I have the Vextenso function in a cell, but the text exits in lowercase, as do to the extent of the value exits in lower case letters. How to use two functions together?

  35. Hi Charles, for this you can create an auxiliary column concatenating the values ​​of these two fields. Then, in data validation, you can look up the reference of that auxiliary column with the concatenated values

  36. I'm actually working with tables in the worksheet, type:
    property sheet
    TAB_EDIFICIOS
    -coluna IMOVEIS (A)
    -column EMPLOYMENTS (D)
    in another worksheet Rent
    I have the columns date, customer, location, value
    in the lease column, I have a property selection box (A) + enterprise (D), but I can not combine the two columns of the table in the same check box.

  37. I did not understand your doubt very well. If you need to get the whole list, just take the interval you concatenated. If you need specific lists, you'll need to create named ranges with these lists so you can filter the buildings separately.

  38. Hi Caius, whenever you need to join values ​​use the same CONCATENAR. I do not know of any other function that delivers this result

  39. Good evening,
    I need your help to concatenate the following data into a table inside the worksheet.
    TAB_EDIFICIOS
    APT --- EDF
    001 - EDF ACACIA
    002 - EDF ACACIA
    202 - EDF ACACIA
    001 - EDF NOBLE
    002 - EDF NOBLE
    202 - EDF NOBLE
    001 - EDF MORETE
    002 - EDF MORETE
    303 - EDF MORETE

    Type I have the same apartment with different building groups but when I register the rent I need to open in the check box / combo the same items by discriminating APT + EDF filtering from the table.

  40. Hello Rafael, I need a concatenated formula that is for example.
    NOTES
    A
    1 1001001; |
    2 1001002; |
    3 1001003; |
    4 1001004; |
    5 1001005; |
    And go all to a single cell result: 1001001; 1001002; 1001003; 1001004; 1001005, today I use the following formula = concatenate (A1; A2; A3; A4; A5), but without changing the notes I need to put the; at the end of each note, if 30 notes are accurate by 30 ";".

  41. Hi Sthefanie, whenever you concatenate, Excel understands the values ​​without specific formatting. In this case, any date is a number that goes from 0 to infinity. To get the result you want, to have the date in the format dd / mm / yyyy, you will have to use a slightly more complicated formula. Assuming that date is in B2, the formula would look like this:
    = CONCATENATE (B1; DAY (B2); "/"; MONTH (B2); "/"; YEAR (B2))

  42. I need to concatenate the A1 cell with a text and B1 with a date

    But strange numbers appear ... help me

  43. Hello Raquel, you can do this = CONCATENATE (TEXT ({your cell with 08}; "00"; ...), so it will always format for 2 digits.

  44. Hi Vanessa,

    I do not know how to do this with the CONCATENATE function. In general, when using this function you take out the formatting

  45. Hello, I need to concatenate two cells, but keep one in italics and the other in the normal form, would you know if this possibility exists? ex genus and species in italics and author's name without being italic.

  46. Hi Raquel, unfortunately the CONCATENAR function ends up generating this type of limitation. If you want to correct you will need to create a formula within the CONCATENATE formula to add a zero before the number in question.

  47. I have created a cell to have two digits (for example, from 8 to 08), but when I do the concatenation it returns to 8 again. How do I solve this?

  48. Hi Mariane, you can create a macro that takes all the values ​​of the column with the result concatenated and just paste the values ​​into another column.

    You would get this result by just pressing 1 macro button.

  49. Hello good afternoon.
    I would like to know how to create a formula that will copy my cell with the result "concatenated" and paste only the result.

    Ex: = CONNECT (B1632; "_"; C1632; "_"; D1632; ""; E1632; "cm")

    Result: 25175_PIZZARIA DONATELLO_FOLDER 21X30cm
    I need to have this end result in a column without having to copy and paste one by one without the formatting.

    Thank you

  50. Yes Rafael, thank you ... I think I did not explain my doubts very well.

    But I was able to reach the conclusion
    and it looks like this:

    A1 [CAIO]; A2 [CEZAR; A3 [SILVA]
    #Formula
    = A1 & "ALT + enter" & A2 & "ALT + enter" & A3

    #RESULT
    CAIO
    CEZAR
    SILVA

    Note: You must enable the text break option to work.

  51. Hi Caio, assuming Caius is in cell A1 and Cezar in cell A2, would be something like

    = CONCATENATE (A1; A2)
    ou
    = A1 & A2

  52. I would like to know how to structure a concatenated text
    for exe:

    Simple Format:
    (1) Caio & (2) Cezar = Caio Cezar

    Desired format:
    (1) Caio & (2) Cezar = (Model below with skipped line, placing first item above second)

    Caio
    Caesar

LEAVE AN ANSWER

Please, write your comment
Please enter your name