Comments on: TEXTJOIN function in Excel to merge text from multiple cells into one

Until recently, there were two prevalent methods to merge cell contents in Excel: the concatenation operator and CONCATENATE function. With the introduction of TEXTJOIN, it seems like a more powerful alternative has appeared, which enables you to join text in a more flexible manner including any delimiter in between. Continue reading →

Comments page 2. Total comments: 81

  1. Please help with suitable formula:

    Input in 3 columns:

    A B C
    Morning Adam Good
    Afternoon Bob
    Evening James
    Alice
    Sam
    Pete

    Desired Result in Column continuous sentences:

    Good Morning Adam
    Good Morning Bob
    Good Morning James
    Good Morning Alice
    Good Morning Sam
    Good Morning Pete
    Good Afternoon Adam
    Good Afternoon Bob
    Good Afternoon James
    Good Afternoon Alice
    Good Afternoon Sam
    Good Afternoon Pete
    Good Evening Adam
    Good Evening Bob
    Good Evening James
    Good Evening Alice
    Good Evening Sam
    Good Evening Pete

  2. Using excel how to get the output from the below input

    Input

    Name Task
    a 1,2,3,4
    b 5,6,7,8
    c 9,10,11,12
    d 13,14,15,16

    Output:

    Name Test
    a 1
    a 2
    a 3
    a 4
    b 5
    b 6
    b 7
    b 8
    c 9
    c 10
    c 11
    c 12
    d 13
    d 14
    d 15
    d 16

  3. How to retain formatting of specific cell or cells while textjoin - for example: out of three cells being joined, only one was bold. In the textjoin cell, I want that value to retain its bold formating

  4. Hello,

    i've been trying using formula's to merge the bundle_Id values in one cell which have similar number to the different sheet but I am not able to merge them. Using =TEXTJOIN(",",true,B2:B9) and which is working well but have to merge the cells individually. Can you please suggest some formula on this.

    Example -
    bundle_id sku formula
    1 75613 1
    1 75176 1
    1 74207 1
    1 74301 1
    1 62750 1
    1 74378 1
    1 74299 1
    1 37738 1
    2 75613 1
    2 75176 1
    2 74205 1
    2 74301 1
    2 62750 1
    2 74378 1
    2 74299 1
    2 37738 1

    Just like this i want to merge all 2 values in one column for 4000 row together. How can i merge them

    1. Hi!
      I am not sure I fully understand what you mean. If I understand correctly, your example has 3 values per line. Explain what you want to merge. Write an example of the result.

  5. Hi,
    I have an SAP extract for Special Instructions.
    In SAP, for the same Special Instruction, I may have 1 or more lines of text and those lines need to be grouped into a single cell for each unique Customer ID.
    What I need to do is combine the cells with a formula (which is the easy part) but I want to the formula to detect when the ID in column A changes to another accountID.
    So 6 lines of data in Cell C1to C6 are combined and the formula detects that the ID changes to a new ID (from 10000 to 10001) and combines cells C7 to C9, and then C10 to C15...

    Cust# Text ID Remit To Text
    10000 0009 Remit to:
    10000 0009 PERKINELMER Optoelectronics
    10000 0009 c/o ROYAL BANK OF CANADA
    10000 0009 1, Place Ville Marie
    10000 0009 Montréal (QC) H3C 3A7, Canada
    10000 0009 Account #403
    10001 0009 Remit to: EG&G Optoelectronics
    10001 0009 P.O. Box 66512 AMS O'Hare
    10001 0009 Chicago, IL 60666-0512 USA
    10002 0009 Remit to: EG&G Optoelectronics
    10002 0009 P.O. Box 66512 AMS O'Hare
    10002 0009 Chicago, IL 60666-0512 USA
    10003 0009 Remit to:
    10003 0009 EG&G CANADA Ltd. Optoelectronics
    10003 0009 c/o ROYAL BANK OF CANADA
    10003 0009 1, Place Ville Marie
    10003 0009 Montréal (QC) H3C 3A7, Canada
    10003 0009 Account #124

    Hope this makes sense! :)

  6. Hi,

    Is there anyway to combine two delimited lists term by term.
    For example cell A1 has 1,2,3,4 and cell B1 has 5,6,7,8. I would like cell C1 to have 1,5 ; 2,6 ; 3,7; 4,8.
    The textjoin function seems to only concatenate cells end to end without being able to enter the cells and go term by term. Any help would be appreciated.
    Thanks.

  7. Hi, is there a way to combine text from multiple columns into one cell, but only returning unique values? I've been using the formula =textjoin(", ", true, G4:R4) which works well but some of the cells have duplicated values. EG:

    O P Q R S
    1.Apple Pear Orange Apple Apple, Pear, Orange, Apple
    2. Pear Pear Pear Pear Pear, Pear, Pear, Pear

    What I'm getting in column 'S' (where the formula is): row1:Apple, Pear, Orange, Apple. row2: Pear, Pear, Pear, Pear but I only want it to show one of each value, so row1: pear, orange; row 2: Pear. I tried to prefix the formula with =unique but that still returns the duplicate word or phrase.

    Any suggestions would be most welcome!

  8. Thankiu so much

  9. Hello I am trying to use this to get a date/ time results however it's giving this result :
    44378.57784,44378.48631,44378.44199

    =ARRAY_CONSTRAIN(ARRAYFORMULA(TEXTJOIN(",",TRUE,IF(Sheet1!A2=C:C,A:A,""))), 1, 1)

  10. Thank You for posting this tutorial.

  11. Is there a way to use a macro to use Textjoin to put multiple types of items in the same cell, with each items having a different color based on a value of type of item?

    So, I have this table, and I want a single cell, where each of the items for the same date are together, but are a different font color depending on the calendar type. So, line 2 and line 5 would be the same color.

    DATE Calendar Time Description UNIQUE VALUE (CALCULATED)
    12/23/2021 Personal 6AM-Fitness Training 44553|1
    12/23/2021 Kids 7AM-Schools closed 44553|2
    12/23/2021 Family 3PM-Holiday Break begins 44553|3
    12/23/2021 Peter 5PM-Jiu Jitsu 44553|4
    12/24/2021 Kids 7AM-Schools closed 44554|1

  12. Hi, the cells returned "N/A" in lookup and return multiple matches in comma separated list, but when i remove some rows in the lookup table, it works. Is there a limit on how many rows in a lookup table this function can handle, how many rows?

  13. Hi,
    Can we edit range as per condition in textjoin.
    Example i have pivot table, in which i have the numbers of the range to textjoin,
    But i have to edit those range as per the pivot value, is there any formula in which the range get automatically selected as per the pivot table value .

  14. Hi! How can I use textjoint to give me the 5 highest values across multiple columns in excel 365? The values should reference to the respective left adjacent cells and each of the five top values should return more than one matching value, if any. I hope I make myself understood

    1. Hello!
      To find the 5 highest values in columns, I recommend using the LARGE function as described in this article. The value from the cell will be returned, but not the reference from that cell. You can combine these values using the TEXTJOIN function as described in the article above.

  15. I have been able to successfully combine my text using the instructions provided, but now I need to copy the combined text into another spreadsheet. How do I do this since the content of the cell is a formula?

  16. #VALUE! error occurs if the resulting string exceeds 32,767 characters.

    Please help to resolve the error.

  17. Hi

    I want to use "Join cells with conditions" this formula When ever i use " =TEXTJOIN(", ", TRUE, IF($B$2:$B$9=1, $A$2:$A$9, "")) " this formula it show "#VALUE!" & i also used "Return multiple matches" =TEXTJOIN(", ", TRUE, IF($A$2:$A$12=D4, $B$2:$B$12, "")) " this one too but it's some times Blank in cell. Please help me in this case

    1. Hello!
      Unfortunately I was unable to reproduce your error. Therefore, the question is not clear to me.
      Please provide me with an example of the source data and the expected result.

      1. When i used TEXTJOIN formula then i saw some of the cell values are incorrect & Some of the cells are blank too. i want to share my excel file for the error but there is no option for attachment.

  18. Hello Svetlana,
    Do you have a suggestion for an alternative or workaround for the 256 char. limit of the if function when used within a textjoin function? I am wanting to join text in column AD if it's corresponding code in column Q is 8. For example, =TEXTJOIN("; ";TRUE;IF($Q$2:$Q$22=8;$AD$2:$AD$22;"")). It works perfectly unless the text in any of the cells is greater than 256 (if function limitation), the formula returns #VALUE!.

  19. Hi,

    I tried using the formula above, but it is giving me all the values in column B separated by a comma

    1. Hello!
      Please describe your task in more detail - what you are trying to find, what formula you used, and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

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