Comments on: CONCATENATE in Excel: combine text strings, cells and columns

In your Excel workbooks, the data is not always structured according to your needs. Often you may want to split the content of one cell into individual cells or do the opposite - combine data from two or more columns into a single column. Continue reading →

Comments page 5. Total comments: 449

  1. Hi,

    I have list of products A to F. If i applied the slicer product A & product B, the table will get filtered with all the records which are having product A & B. However, i need only value of product A & product B and it should be updated with the separation (semicolon).

    I have tried =CONCATENATE(A1,";",A2,";",A3,";",A4,";",A5,";",A6,";",A7,";",A8,";",A9,";",A10) but here unable to check the unique value and its not pulling the filtered columns. Instead all the products from A1 to A10 its getting updated, while only Cell A2, A4 are having product A and A8,A9 are having product B.

    Could you please guide on how to achieve this.. Thank you once again..

  2. In "Column A" I have a Concatenated Value of "Column B"|"Column C"
    In "Column B" I have a list of Names
    In "Column C" I have a list of Sites
    I want to able to search on the concatenated value but Excel isn't able to find the result when I try to copy & paste...

    Ex:
    | A | B | C |
    1 |Martin|Montreal |Martin |Montreal |
    2 |Jayme|Jamaica |Jayme |Jamaica |

    So if I do CTRL+F and search for "Martin|Montreal" Excel doesn't find it.

    I have tried the concatenate function: CONCAT(A1,"|",B1)
    I have tried simply concatenating the text: (A1,"|",B1)
    I have tried TEXTJOIN("|",FALSE,A1,A2)

    All with the same result, if I search for the string in question or do lookups on the value, it cannot find it?

    Does anyone have any tips on how to concatenate values and be able to use them in VLOOKUP or other lookup functions?

    1. Sorry it didn't display to good but values are as such:
      Column A: Formula concatenating the strings in column A and B with a "|" separator
      Column B: Martin, Jayme
      Column C: Montreal, Jamaica

  3. Hello -
    After I complete a concatenation I would like to delete the source data without affecting the resulting text string. How do I do this?

    1. Hello Neli,

      Thank you for this good question!

      Just replace your concatenate formulas with their values. For this, use Excel's "Paste special - values only" feature. The detailed steps can be found in How to replace formulas with their values in Excel. I've added this tip to the tutorial for others to know.

  4. Thanks very much for tutorials, I like to know if there is a way to make just a cell behave as a normal calculator.
    i.e this cell should be capable of summing figures that appears in another cell, while keeping last cumulative figure visible.
    This other cell will be the key-in cell or active cell.
    Example:
    Cell 1: =2*5, Answer appears in Cell 2,
    Cell 1: =3*6.8, Answer is added to the previous value resulting from (2*5) and still appears in cell 2.

  5. Dear,
    I have 2 thing in a one cell of excel,"Words and numeric" how can i separate numeric and words in other 2 cells of excel,

  6. Please help me with a formula to return value(phone number)(with phone number format)from merged cells in another worksheet.

    IF($A$3="","",CONCATENATE('Work Order'!R4,'Work Order'!S4))

    Thank You!

  7. Please help for for number to words format using trim (concatenate)
    Ex. 10,000.00 will convert to words is
    *Ten thousand pesos only*
    10,000.50
    *Ten thousand pesos and 50/100 only*

    Thanks in advance!

  8. hi i have two columns with different value , need answer as shown
    having
    a 3
    a 4
    a 5
    b 2
    b 1
    c 3
    C 3
    Need Answer like :
    A 3,4,5
    B 2,1
    C 3,3

  9. Hi All,
    I have doubt in & function.
    Plz solve the issue.
    Eg : Invoice No. 8951 in A1 , Invoice date 20-03-2019 in A2 , \ in A3
    If i use & function in Excel, its shows 8951\43544
    my expectation is 8951\20-03-2019
    Is possible in Excel ?
    Plz revert

  10. Im so stumped to what this (below) indicates?
    "="&
    the formula it is used in is shown below.
    =SUMIF(logTable[Date],"="&D16,logTable[Amount])

    My question is, can i just eliminate the ("="&), i am receiving the same output without it? What does it indicate? If you need more information, i can provide.

    1. I apologize for the bluntness, thank-you so much for the opportunity to ask the question. This site so informative and helpful in every way. Excel is so challenging but, with your help, it is definitely manageable!

  11. How to concatenate specific words by excluding some specific words in excel. For e.g. If there are words like : A, B, C, FALSE, D. So how can i concatenate these to get the result as A_B_C_D

  12. could you please let me know how to get 300 number of rows data into one cell?

  13. i want to single cell value into multi cell

  14. I am trying to add +10 in a cell without adding 10 days to it. I need it to show +10:00. Any suggestions how I would do this?
    2019-08-04T02:30:00
    to
    2019-07-25T02:30:00+10:00

  15. How can I concatenate a sum of different sheet tab?

  16. cell a1 contains the value: 1992 - 1996
    cell b1 contains the formula: =CONCATENATE("=",A1)
    cell b1 displays: =1992 - 1996
    What I want is for cell b1 to now be treated as a formula and compute and show the value: -4
    Can that be done?

  17. I know there is a way to set a fixed cell for concatenation when combining multiple rows and columns of data. I have seen it done, but I can't remember how, and I can't find any examples.
    Here is an example of what I would like to do:
    Column A is the concatenated value
    Column B is a series of Levels starting in Row 2. Level 1, Level 2, Level 3 etc.
    Column C Row 2 has the word MECH
    Column D Row 2 has the word PLAN
    In the end I want Column A to show "Level 1 MECH PLAN" in Row 2, "Level 2 MECH PLAN" in Row 3, etc. How do you write the concatenation formula so that no matter what row it is in, it calls for Column C Row 2 and Column D Row 2, while calling for Column B whatever the current row is.
    Hope this is clear.... Thanks!

  18. Hi,
    I'm trying to automate something wherein I need to display upper case characters, as in abbreviations or acronyms to be displayed as upper case characters, separated by space.
    For examples, if I enter "IRR" it should display "I R R" in next cell.
    Your help would be greatly appreciated.
    Thank you.

  19. I really need some help i would like to take several dates MM/DD/YY that are like E2,F2,G2,H2 and so on and have them look like this 01/01/19, 01/02/19, 01/03/19, 01/04/19 is there a way i can do that ??? please help if you can

  20. I want to concatenate column cells from 1 to 5 in such a way that it should neglect the same row cell value. Please refer the following table here data1 to data5 are 5 cells of a column and in front of the cell is the required result of concatenation.
    data1 data2 data3 data4 data5
    data2 data1 data3 data4 data5
    data3 data1 data2 data4 data5
    data4 data1 data2 data3 data5
    data5 data1 data2 data3 data4

  21. I have a cell (B2). cell value is: SAHANSRA,NAVJOT This is Lastname,Firstname
    I am using: LEFT(B2,FIND(",",B2)-1) and MID(B2,FIND(",",B2)+1,10) respectively to pull the name apart and place them into separate cells.
    What I need to do is place the LASTNAME into a static array with spaces to fill for 30 characters total. The results of the Lastname formula is now in cell N25
    REPLACE(N25,(LEN(N25)+1),(30-(LEN(N25)))," ")
    I need the result of the last formula to look like "SAHANSRAbbbbbbbbbbbbbbbbbbbbbb"
    where "b" is a space (22 of them) I thought this would give me my 22 spaces, but it only gives me 1.
    HELP!! thank you in advance.

  22. hi, how i can use concatenate with condition?
    for example i have 2 columns A & B and the condition is if we have same number in column B with different name in column A, and i need to add them in same cell (C).
    do we have i formula?
    A B C
    elie 1 elie,Rita
    Rita 1 elie,Rita
    Joseph 2 Joseph,Noha
    Noha 2 Joseph,Noha

  23. I have a question with what I am finding is a difficult if/then situation.
    Say I have a column with 3 separate values over hundreds of rows: AA BB or CC
    AA is unique but BB and CC ultimately will be the same.
    I need to concatenate 3 cells based off of the above breakdown.
    The first part will be DEF: (with the colon) which is common for all of the outputs.
    The next part will be either GHI:: or JKL:: depending on AA, BB or CC with JKL:: being associated with BB & CC designations.
    Then the last part is a unique number that will be on its own ######.
    The end result will need to be DEF:GHI::###### (AA value) or DEF:JKL::###### (BB or CC value) all depending on the values AA BB CC.

    Keep hitting a wall with figuring out the IF AA then GHI:: portion.

    Thanks,

    Mike

  24. How do I format after using concatentate using a formula for the two values?
    For example: =concatenate((d3/d4)," / " (d5/d4)) gives values like:
    1.425212521521 / 1.4526265654
    But, I only want these values to be 3 numbers after the decimal. Is this possible if the returned value is a text string?

  25. Hello I want to use concatenate function
    0000 1 /15-16

    I want 00001/15-16

    I am not getting

    1. Set Number Format to General.
      =CONCATENATE(A1,B1,C1)

      Regards,
      Taimoor

      1. Also Set Number format for your; 0000 to Text.

        Regards,
        Taimoor

  26. Hi there, I've been trying to combine these into one cell without losing the zeros infront of columns C & D but have failed. Can you please help?
    A1 B1 c1 d1
    P HHL 0007040 0114876823
    Thank you!

  27. 1 A Banana Closed
    2 B Apple Closed
    3 B Banana
    4 A Apple Closed
    5 A Apple Closed

    Dear Friend, Kindly help me out in above i want a formula in which excel itself concatenate values of column 1 in which all other values are same, like i want answer in one cell like (4,5) , because its other column values A Apple Closed are same.

  28. how to concatenate different characters within a cell???

  29. How can I combine values from different cells in one column?
    Eg.
    A B C D
    20 50 80 A = 20, B = 50, C = 80

  30. How do one sum up a value like this 75+17+46+05+28 already in a single cell.

    you find a lot of value like this in a single excel cell, how do one add it up 83+24+32+79+74

    53+21+59+82+65

    02+54+33+84+16

    86+22+59+34+04

    57+03+27+39+54

    1. Just add a + sign at the beginning

  31. Hi,

    I want to limit the content of a cell to the following format:

    XX YY 00-ZZZZ-EDWW

    How can i do it using Data Validation or any other way?

    thanks,

  32. I've used the Concat function and joined three cells successfully. Now, how can I cut and paste the "merged" cells into a word document?

    1. Hi Barbara,

      In Excel, select the cell with your CONCAT formula, and press Ctrl+C to copy it. Switch to your Word document, place the mouse pointer to where you want the merged text to appear and press Ctrl+V to paste it.

  33. Hi,
    I had a Excel sheet which has many columns . Let say A1,D1 and G1 are has to be concatenated.

    A1 has value 134574935
    D1 has date in 03/05/2018
    G1 has date in 05/05/2018

    I want result as
    insert into tablename values('134564935','03-May-2018','05-May-2018');

    Note:- date format has changed and the delimeter in the format has also changed.

    Please help me to solve this . Thanks in advance .

  34. I'm trying to concatenate two columns of data. One contains area codes for phone numbers, and the second column contains the numbers themselves. The problem is that when the phone number begins with a zero, this isn't included in the final concatenated value, resulting in nonviable phone numbers. How can I go about fixing this?
    Thanks

    2. Hanelise:
      If you want to keep the leading zeros, you just need to format the cells as Text before entering the data. This should be OK for you because when you concatenate the cells they will go to text anyway.

  35. Thank You - Wonderful article - its exactly what i wanted.

  36. Hi,

    I need to concatenate a few cells but A2 it's a number 08 and I need the zero in front, but for some reason it's only showing the number 8 alone, same with all the single digits like 4 it should be 04. For the 2018 I need only the 18 but it's giving me the complete year.

    8_4_2018_

    =CONCATENATE(A3,"_",B3,"_",C3,"_",S3,"",Z3,"_",K3,"_")

    Correct way should be:

    08_04_18 It's the date but in this format.

    Thanks

  37. Is there a quick way to build a formula:
    =concatenate(a1,a2,a3,a4 .... a1024)
    or
    =concatenate(a1,b1,c1,d1, ... amj1)
    without typing the addresses one by one ?

    Please note, that the cells a1..a1024, or a1..amj1, contain results of other formulas, so their content varies, and the TRANSPOSE trick seems not to work :-(

    BTW, most of the above internal results will be just empty strings, and the rest will be just one or two letters, so the actual result of the above concatenate will be only 100-200 characters.

    Bests,
    Mike

  38. What is my text has a "READY TO LET" text, and I like to join text and formulas like :

    ="INFLATION TO [READY TO LET YEAR] @ 4% ("&I64&" years)", I get this:

    INFLATION TO [READY TO LET YEAR] @ 4% (4 years), but I need this:

    INFLATION TO "READY TO LET YEAR" @ 4% (4 years)

    Would you please let me know how to write this formula?

    Please advise,

  39. Hi there,
    How to concatenate two columns that mimic this (N'Doe, John', N'Doctor') where Doe, John is the first column and Doctor is the second column. This (N'Doe, John', N'Doctor') has to be used in SQL and XML. Thanks

  40. Thanks. This is what I really wanted

  41. Hi

  42. Hello Sir/Madam,
    plz solve my problem that how to write many dates in a cell in excel sheet since a have a range of dates for example here Mr. Ram has taken 6 spells leave in a month as

    SN NAME DAYS FROM DATE TO DATE

    1. Mr.Ram 5 07/05/2018 11/05/2018
    2 18/05/2018 19/05/2018
    3 21/05/2018 23/05/2018
    2 25/05/2018 26/05/2018
    2 28/05/2018 29/05/2018
    1 31/05/2018 blank

    2. Mr.Paul 1 02/05/2018
    3 05/05/2018 07/05/2018
    2 11/05/2018 12/05/2018
    1 15/05/2018

    3. Mr.Mac 3 18/05/2018 20/05/2018
    3 26/05/2018 28/05/2018

    4. Mr.Bond 7 23/05/2018 29/05/2018

    sir/madam,
    i want to write all DAYS in a cell like above and all FROM DATE in a cell like above and also TO DATE in a cell like above.

    sir i want that if there is only two spells of leave of an employee there shows only two lines in cell and if there is three spells of leave of an employee then there shows only three lines in cell AND if there is only on day of leave then shows in only from date cell and to date will blank as shown in 6th spell as 1 day 31/05/2018.

    please HELP ME SIR/MADAM

  43. NL01AB1895

    i just want above result as per below give.

    1895_NL_01_AB

    Also, i want a particulars word search in a column in excel.

    So, Please guide us.

    1. Hi Drishti,

      I'd simply use Excel's Replace All feature:

      1. Press the Ctrl + H to open the Replace tab of the Find and Replace dialog.
      2. Type /node in the "Find what" box.
      3. Type /in-en/node in the "Replace with" box.
      4. Hit "Replace All".

      Done.

  45. How to do?
    32
    12
    32
    32
    32
    92
    36
    192
    96
    36
    24
    32
    32
    32
    100
    32
    32
    384
    32
    32
    to this

    32,12,32,32,32,92,36,192,96,36,24,32,32,32,100,32,32,384,32,32
    (in one shot without using one by one concatenation) .

    1. Manisha:
      I use the Concatenate Transpose method on the infrequent occasions I have done this. I decided to look for other methods to answer your question and came across this excellent article on five different methods to accomplish your task. Take a look at the article and I know you will find a method that best suits your situation.
      https://excelchamps.com/blog/concatenate-a-range-of-cells/

  46. I am trying to reference other cells in excel that have formulas for an API. The following formula works: =RTD($B$1,,"srpt//OPT/20180629/p/137/USD", "Bid")

    But when I replace certain parts with referencing, it does not work:
    =RTD($B$1,,"B4//opt/$H$2/p/G4/usd","bid")

    How do I fix this?

    Kind Regards,
    Liviu

  47. It's very usefull. Thanks !

  48. All the things very nice....I were looking for concatenate function with carriage return and my luck & pleasure that I visited this page :)

    Got a very nice and easy solution.

    Thank you.

  49. hi svetlana will you assist me to make letter lower case to uppercase formula for example I want" formula" text " rmul only to uppercase mean ruml lowercase to upper case RUML thank you

    1. Paban:
      Where the original text is in A1
      Enter =UPPER(A1) in an empty cell.
      The function has an expanded explaination in the article above.

  50. I have a large data that are over 13K rows, I want to combine all of them into one cell. I know you can only have 8192 characters in an Microsoft Excel cell. I want to know if anyone know how to create a VBA macro or better way to have a formula that can do the job. Right now each cell has 9 characters in each cell so I am combining about 3200 cell at once, but I have to do this over and over again until i finish all my 13K records. I need to separate them by comma (,).
    For example: 123456789,123456789,123456789,123456789
    That is how I want it but I know I could only do 3200 records at a time.
    My original records looks like this:
    123456789
    123456789
    123456789

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)