Share via

Formula to combine text from multiple rows of text into a single cell, without duplicating text from previous rows

Anonymous
2024-10-28T01:42:13+00:00

I have a list of data with long text that excel split into separate rows. I want a formula to combine each text to a single cell without duplicating the previous cell data above. Would really appreciate the help. Example shown in the table below :-

Data Result
Apple Apple Orange Lemon 123
Orange
Lemon
123
Orange Orange Orange Lemon
Orange
Lemon
Apple Apple Apple Apple 1 2
Apple
Apple
1
2
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

10 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-10-28T23:57:29+00:00

    [ Edited:] It only works for 365, not 2019. Sorry.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2024-10-28T05:31:55+00:00

    Hi Andyosk4567,

    Thanks for your post in Microsoft Community.

    There are several ways to concatenate text:

    1. Concatenate directly using the & symbol: 
      Enter this in cell B2: 
      =A2&" "&A3&" "&A4&" "&A5

    If you don't need spaces as separators, you can remove them (" ").

    [![](https://learn-attachment.microsoft.com/api/attachments/875b9af5-a428-4934-b331-e0b4c5907ac6?platform=QnA"2">

  3.  Use the CONCAT function:   
    
Enter this formula in cell B7:   
    
=CONCAT(A7:A9)  
    4.

This is a very convenient and quick method for concatenation, but the downside is that you cannot choose a separator.

[![](https://learn-attachment.microsoft.com/api/attachments/e1c1a1f4-b334-499c-b4c9-486c682ee91f?platform=QnA"3">

  •  Use the TEXTJOIN function:   
    
Enter this formula in cell B11:   
    
=TEXTJOIN(" ",,A11:A15)
    • 


    With TEXTJOIN, you can freely choose the separator you need; you just need to enter it in the first parameter.
    

    [![](https://learn-attachment.microsoft.com/api/attachments/16b51bf8-db3c-4721-9718-9f9c79c81e49?platform=QnA

    1 person found this answer helpful.
    0 comments
  • Anonymous
    2024-10-28T12:15:57+00:00

    Hi,

    online sql:

    create temp table aa as

    select fillna(iif(lag(f01) over () is null or lag(f01) over () like '' or unicode(lag(f01)

    over()) =160,rowid,'')) grp,* from Sheet1 ;

    //select * from aa;

    select f01 Data,iif(row_number() over ( partition by grp)=1,group_concat(f01,' ') over (partition by grp),'') Result from aa;

    0 comments
  • Anonymous
    2024-10-28T06:53:40+00:00

    Are you hoping to merge the entire column A into one single cell? Or do you still need to differentiate between various sections, such as merging A2 to A5 as one part, and A7 to A9 as another part?

    Please share more context with me, or alternatively, I have enabled private messaging for you. You can share a screenshot of your worksheet or the file with me so that I can better understand and provide you with a more tailored solution.

    Thomas

    0 comments
  • Anonymous
    2024-10-28T06:26:45+00:00

    Thanks for responding Thomas, however my issue isn't just a few rows. I know these formula's you mentioned but for my scenario I have thousands of columns to combine each with different number of rows of text. So what I want is a formula where I can paste through the whole sheet so i wouldn't need go manually go through each item. Hope that helps.

    0 comments