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

Question

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

Answer 1

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">
 Use the CONCAT function:   

Enter this formula in cell B7:   

=CONCAT(A7:A9)  

Answer 2

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.

Answer 3

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

Answer 4

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;

Answer 5

Hi, where do I key this formula in excel ?