Based on your description, you wonder to merge text data in excl. As far as I know the Consolidation feature of Excel will not consolidate text. The only text that Consolidation will bring across is Category Titles. Titles are brought into the destination
sheet when you select Top Row or Left Column from the Consolidate dialog box. Any other text in the source areas is not brought across. If the source areas contain text other than Category Titles, blanks are brought into the destination area.
Now I test your issue in my own environment. And I got two results as shown in the following figures.
For this result, I merge the text data without the title of worksheet2. I use VBA code to get this result.
Dim J As Integer
On Error Resume Next
Sheets(1).Name = "Combined"
For J = 2 To Sheets.Count
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
My VBA code might be not perfect, you could refer to this article about consolidating data from multiple worksheets into a summary worksheet in excel.
If you need more help about code, please post your issue in MSDN forum for Excel.
Then I test another method with an add-in, and you can get the result with whole contents in 2 worksheets. As shown in the following figure.
For this result you can refer to this article and get more information about this result.
Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.
Hope its helpful.
TechNet Community Support