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 7. Total comments: 449

  1. Hi

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

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

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

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

  7. It's very usefull. Thanks !

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

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

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

  11. I may not know the right word to use pls pardon me:
    My name is Apple
    Joy
    Peace
    goodness
    love
    sound
    mind

    I want Column 1(My name is) to be linked with each of the Column 2 (name list) above in a new column(Column 3) Thanks

  12. I want to use a concatenation formula. Pulling information for several cells the cells either have a date for example cell F4 or are left blank. I want the formula to pick up the date or put in “no date at this time” and I’m not sure how to make the formula work.

  13. I want to use a concatenation formula. Pulling information for several cells the cells either have a date for cell F or are left blank. I want the formula to pick up the date or put in “no date at this time” and I’m not sure how to make the formula work.

    1. Elizabeth:
      Will this work?
      Test to see if cell is empty, if it is then return "No Date At This Time", otherwise return the cell's contents.
      Where the data is in cell A18, it looks like this:
      =IF(ISBLANK(A18),"No Date at this time",A18)

  14. Mr.Doug
    Thank you for reply
    but i want result is 010155
    this result is 1155

    1. That sounds like a cell formatting issue.
      Select the cells and choose format cells and then choose custom and use one of the provided formats or use one as a starting point and then change it to the format you want.

  15. I want to concat A1 cell value 01
    B1 cell value 05
    c1 cell value 5
    how to concat
    I want result d1 cell 01055
    Please reply me

    1. D1=Concatenate(A1,b1,c1)

  16. COL1 COL2

    1 AAA
    1 GGG
    1 FFF
    2 GGG
    2 HHH
    3 RRR
    3 JJJ

    concatenate text based on criteria in Excel? FORMULAS

    1. Not sure what the criteria is, but here's a way to do this.
      If numbers are in cells A1 through A10 and letters are in cells B1 through B10 and your criteria is if a cell contains a "1" then:
      =IF(OR(A1=1,A2=1,A3=1,(Concatenate(B1," ",B2," ",B3))

  17. thanks for the support

  18. Thank you!

    While I'm pretty sue, that there is an easier way to do what I need (replace input value with text including said value), probably even in single column (yet no tutorial, how to do that, with concatenate function I achieved my goal:
    =CONCATENATE("OAM-";C2;"/PP-2017")
    And even expanded it for future use (albeit at the cost of 2 extra columns *sad*):
    =CONCATENATE("OAM-";C2;"/";D2;"-201";E2)
    Result (where XXXXX is the input number):
    OAM-XXXXX/PP-2017

  19. Hello!
    How do I concatenate based a number such as...

    N = 5
    String = "Hello"
    Desired output = "xxxxxHello"

    N = 3
    String = "Hello"
    Desired output = "xxxHello"

    Thanks

  20. here is one i am trying to do...
    I have a pricing matrix and i have named each cell in the matrix by its location within the matrix, example...
    A1=ABa1, B1=ABa2, C1=ABa3
    A2=ABb1, B2=ABb2, C2=ABb3
    A3=ABc1, B3=ABc2, C3=ABc3

    so on another sheet i have my products that are coded with these name. example...
    Product A: S1234a1
    Product B: S4567b4

    i want to strip that "a1" off the end and then use it in the formula to show the price for ABa1.
    I use "RIGHT" formula to grab the 'a1' text, and then i use CONCATENATE with plain AB text to create the value ABa1 displayed in the cell. so thats great i can see a string on data that is exactly what i want

    so heres my dilemma...
    in any cell, if i manually type in "=ABa1", it refers to the cell which i named ABa1 and the value within that cell is displayed. makes sense works perfect...

    but what i want to do is use my CONCATENTATED string of data, rather that actually keying in the my "=ABa1".

    somehow like this....=(the concatenated data string i want use), and then will have the same affect as when i manually key it in.

  21. Thank you for this excellent guide. I have a series of concatenated sentences each with text intermixed with values (numbers) pulled from other cells. In some cases, I have used the TEXT function to render the desired number format. However, I am looking to make the values (whether or not with the TEXT function) to pop out of the surrounding text in a contrasting colour. How can I format it to do this? I was hoping the TEXT function would enable this, but cannot see how. Thank you.

    1. Hello,

      Please note that the TEXT function does not support color formatting. So if you copy a number format code from the Format Cells dialog that includes a color, e. g. $#,##0.00_);[Red]($#,##0.00), the TEXT function will accept the format code, but it won't display the color.

      Hope it will help you.

  22. Help

    Imstruggling to make 2 formulas based on these digits:

    9055170120100

    1) I need a formula which will turn it into this:

    05517 012.010

    -Which is remove the 9
    -Then space after the 5th digit
    -Add decimal after 7th digit

    and 2)

    I also need to turn a different cell into this:

    012.010

    -which is remove the first 6 digits
    - Then add the decimal in the same place as above.

    Any help woul be much appreciated!

  23. How can change names in single cell of a spread sheet from lastname, firstname
    entered just like that, with a comma. I want to cell to give me the names firstname lastname with no comma. Is that possible?

    1. You can do this by getting rid of your comma's. To do this, select all the names and then go to the "Data" tab and click on "Text to Columns". In this menu, you will make sure that "Delimited" is selected. Then click Next. On the next screen, make sure that only the "Comma" box is selected, then click Finish. When this happens, your single cell with LastName, FirstName will become two cells with LastName in the first cell and FirstName in the second cell. Then just use =CONCATENATE(FirstName Cell," ",LastName Cell) to get your desired results. If you want to be able to get rid of the first two columns, select your new results and copy them. Then under Paste Special, select paste values only.

  24. URGENT! - I have concatenate(C2, " ",B2),I have copied the data in the remaining fields.
    Now I need to REMOVE fields B & C without obtaining the #REF! error

  25. hi:

    i need any help please
    i have a single column with 2322 cells, and want to combine the data for each 20 cells together on next column and continue

    br
    Shahad

  26. Hello,

    I have set up my formula and it covers a large range. I separate the texts with commas. How do I remove the excess commas at the end, without shorting the range? Thanks in advance!

  27. I'm combining number cells. One of the number cells is formatted special to show three digits (000). But when I combine them the first zeros disappear. So, 001 in that cell needs to be displayed as 001 (which is exactly the number in that cell) not as 1 (single digit).

    How do I combine them and still keep my cell formatting the same.
    Thanks

    1. I'm struggling with the same issue. I have several cells that I need to combine with hyphens to use as a budget code, but some of them are formatted as text and some of them are formatted "special" with a certain number of digits like you described. Some of the columns with a leading "0" are formatted as text, but I can't for the life of me figure out how to change the others without losing the leading zero again. Any help would be appreciated!

      1. To keep the 0's in front of numbers, put a single quotation mark in front of them like this:

        '00....

  28. I have 2 columns Singer 1 and singer 2. I want to concatenate with comma separator. However in some rows only Singer 1 Values are available and if there is no value in singer 2 - I dun want the comma separator to be posted since there is a single value.

    For better understanding:
    Scenario 1 -
    Singer 1 - Jolly
    Singer 2 - Nerdsk
    Result of concatenation - Jolly, Nerdsk

    Scenario 2
    Singer 1 - Jolly
    Singer 2 -
    Result of Concatenation - Jolly,
    The result I m looking for is - Jolly without a comma

    Hope this explains what I am trying to convey.
    Do let me know a solution that I can implement.
    Thank You In Advance
    Rgds
    Ankur

    1. Use the below formula.
      =IF(ISBLANK(A2),A1, CONCATENATE(A1,",",A2))

      Regards,
      Taimoor

  29. Used CONCATENATE(A1:A3 & " ," ) to combine cells with a separator it is working fine(Used F9). Now i need to apply for all rows but it is not working. Can you help here. Thanks

    1. Hi Selva,

      Please try one of the following formulas:

      1. =CONCATENATE(A1,",",A2,",",A3,",")

      2. If you use Excel 2016, then you can try this array formula:
      =CONCAT(CONCATENATE(A1:A3,","))
      Please don’t forget to press Ctrl + Shift + Enter to complete it. Once you do this, Excel will automatically enclose the formula in {curly braces}. In no case should you type the curly braces manually, the formula won't work.

      Hope this will help you.

  30. Goal: Take information from cell A2 on sheet "Ref", then use that to concatenate that information to set a reference list for sheet "2".

    Cell 2!A40: =Ref!A2

    Cell 2!B40: =if(B40=0," ","=Ref!b"&A40)

    So, when sheet "Ref" has something in Ref!A2 then 2!B2 will show what is in cell Ref!B2)

    I want to then be able to copy sheet "2" to make sheets"3","4","5", etc. and have Sheet "3" show what is in cell Ref!B3 and so on for each following sheets.
    Thanks

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  31. How to make new row in one cell or make alt+enter in concatenate formula

  32. Hi there,

    I am trying to use the concatenate function on two cells, one that is formatted as a number and another cell that is formatted as a percentage.

    It seems the new combined cell doesn't maintain the % formatting, so I am just ending up with a number and a very long decimal.

    Is it possible to keep the % formatting in the combined cell?

    Thanks,
    Billy

    1. were you able to figure out this?

  33. Never mind, I solved it. Thanks.

    1. How did you solve it? I think I have the same question/problem, but haven't figured it out as of yet! Thanks

  34. in addtion to my question above, I format column C as General, not Text.

  35. I have a sheet formatted as a table, but when I try to use CONCATENATE it display the function text not the result of the function.

    For example:
    in cell A1 (header): CodeFirst
    in cell A2 (text value): AA
    in cell B1 (header): CodeSecond
    in cell B2 (text value): BB

    in cell C2: =CONCATENATE([@[CodeFirst]];[@[CodeSecond]])
    it display as it is, not AABB

    any ideas?

  36. Step 1 Sort names Alphabetically
    Step 2 Give 1 2 3 besides the names you wan't in order
    step 3 Sort selecting names & number by column having number ascending
    step 4 Use concatenate function for your numbered cell separating","
    =CONCATENATE(C2,",",C3,",",C4,",",C5,",",C6,",",C7,",",C8,",",C9,",",C10,",",C11)

  37. i Have 20 list of name like

    Kim
    Solonin
    Gafarova
    Ayubova
    Danilenko
    Dityatyev
    Agakov
    Karavaev
    Protopopov
    Bohn
    Williams
    Bhatia
    Miller
    Gass
    Townsend
    Hohberg
    Lai
    Nazarewycz
    Singh
    Ooi

    i want only 10 name in a single cell like kim,Solonin,Gafarova,Ayubov, etc
    using formula how ?

  38. Is it possible to concatenate data from columns based on a single reference column, so where the customer is the same name, concatenate all the references from each line into a single row:

    i.e.
    Input:
    A B
    27918 Select
    27922 Select
    27920 Select
    27921 Select
    27919 Wholesale
    27923 Wholesale
    27924 CSP Supplies
    27925 CSP Supplies

    Output:
    A B
    Select 27918,27922,27920,27921
    Wholesale27919,27923
    CSP 27924,27925

    Hope that makes sense?

  39. how do i concatenate below:

    1001
    1002
    1003
    1004
    1005
    1008
    1013
    1014
    1015
    1016
    1020
    1021
    1025
    1026
    1027
    1028
    1029
    1030

    to this format:

    1001 thru 1005, 1008, 1013 thru 1016, 1020, 1021, 1025 thru 1026

    thanks

    1. 1- Select the cell where you need the result.
      2- Go to formula bar and enter data range ex. =TRANSPOSE(A1:A5)&” “
      3- Based on your regional settings, you can also try =A1:A5 (instead of =TRANSPOSE(A1:A5)).
      4- Select the entire formula and press F9 (this converts the formula into values).
      5- Remove the curly brackets from both ends.
      6- Add =CONCATENATE( to the beginning of the text and end it with a round bracket).
      7- Press Enter

  40. what is the excel formula to reflect "8/1/2017" as "Aug"?

  41. Hi Aland,

    I have an excel table (C4:O12) that sums values W-L-T (win-loss-tie) records both horizontally and vertically when entering a "W", "L", or "T" in each cell in table with a resulting "0-0-0" format. I have the totals with this format in bottom row from C13:O13 as well as in column from P4:P12 Here are the following formulas Ive used:

    horizontal
    =CONCATENATE(COUNTIF(C4:O4,"W"),"-",COUNTIF(C4:O4,"L"),"-",COUNTIF(C4:O4,"T"))

    vertical
    =CONCATENATE(COUNTIF(C4:C12,"W"),"-",COUNTIF(C4:C12,"L"),"-",COUNTIF(C4:C12,"T"))

    How do I sum the totals for either column O, or row 13 to get an overall total in the same "0-0-0" format?

    THANKS !

    1. Hi, David,

      would it be possible for you to send us a sample workbook with your data and formulas you're using to support@ablebits.com? Don't forget to include the link to this comment.
      We'll look into the way your data is stored and try our best to help.

  42. Hello all,
    Is there a way concatenated results can be searchable (ctrl+find) and/or filterable?
    Thank you in advance for any tips :)

    1. Although this question was a bit more than a year ago, I too was attempting to figure this out and landed here. Excel can natively do a find of this information. With the native find window open select "Options<<" from there make sure you select Look in: "Values". The default is "Formulas" however this will not produce the results you are looking for. Hope this helps someone.

      1. thank you Aland for above info.

    2. Hello, Aland,

      sure they can :)

      As for searching, you can use our Advanced Find and Replace add-in. You can download its fully functional trial version here and see if it works as you need.

      As for the filter, you should be able to use Excel built-in one, but if for some reason it doesn't work, you can try one from our collection of Quick Tools.

      Hope they help!

  43. How would you combine values from multiple cells separated by commas only when values exist?

    Example

    A B C
    1|George Herman Ruth
    2|Jackie Robinson
    3|Mark Marche McGwire

    Row 1= Concatenate(A1,",",B1,",",C1) = George, Herman, Ruth
    Row 2 = Jackie,,Robinson
    I don't want a comma when no values exist to separate.

    1. Hi, was this question ever resolved, I have the same issue
      1 Major;Double Major
      2 Major
      3 Major;Double Major

      1. The double comma is there because the middle cell is blank. Try this:

        =IF(B2="",CONCATENATE(A2,", ",C2),CONCATENATE(A2,", ",B2,", ",C2))

  44. Hi All, This is my actual formula =IF(F$1,'Apr-2016'!J16,"") "Apr-2016" is the name of the sheet in the workbook, i would like to use concatenate so I can choose the year (2016) from another cell. please help many thanks

  45. how to use concatenate for (bluedart 5565565 24-Jul-17) in one column.. data in brackets are in 3 different column.

  46. SREI $ 41,26,000 54 days
    how to use concatenate for figure including comma = Devid-$41,26,000-54days

  47. sry, i missing something
    actually the SOURCE DATA IS
    oldno54,newno4355,eaststreet,virudhunagar,mutharpatti,sattur,greengarden,chennai,tn,tamilnadu,600018

    and the excel format is,

    ADDRESS1 ADDRESS2 ADDRESS3
    oldno54,newno4355 eaststreet,virudhunagar mutharppati,sattur

    ADDRESS4 CITY STATE PINCODE
    greengarden,chennai tn tamilnadu 600018

    so please help me,solve this problem immediately.

  48. hi, i am sonai.i have one question please answer it.
    the format of the EXCELL is,
    ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 CITY STATE PINCODE
    so, split the address to this format,
    the source data is
    oldno54,newno4355,eaststreet,virudhunagar,mutharpatti,greengarden,chennai,tn,tamilnadu,600018
    please, solve the problem.it's my humble request youto.immediately

  49. This is great, I understand the concatenate and the and operators better now. Thanks a lot. WOW!

  50. I have inserted a row and typed in the formula

    =CONCATENATE(J1," ",K1)

    and have combined the first and last names of my clients. How do I now place the combined data into another cell without showing the above formula in the Excel formula bar?

    Keith

    1. I copied my combined names and then did a Paste Special into a new column. I tried a few choices until the right one popped up.

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 :)