Forum Discussion
Combining Text Strings with Ampersand (=a1&&" "&a2 formula) cuts off after 32,767 characters
Cat-C , glad it helped, good luck with your project.
Depends on what you would like to do with so long text. If just to display, workaround could be to generate such text in Text Box or in Word and insert it into Excel as object. But that's very static and very limited solution.
If concatenate texts in Text Box like
you may do with VBA, in simplest case
Private Sub TextBox1_Change()
TextBox1.Text = Range("D3").Value & Range("D4").Value
End Sub
You may google for more details about Text Box ActiveX Control.
As variant you may use Power Query. Not necessary to concatenate the cells. Query the range with such cells, split column by space, unpivot all columns in the result.
Some headache you need to clean resulting words - trim them and remove punctuations. Finally Group and count, return result to Excel.
First sample I found is Analyzing text with Power Query and Excel- The Excelguru BlogThe Excelguru Blog That's not exactly what you need, but could illustrate an approach. Perhaps more exact patterns exist.
SergeiBaklan Thank you so much, and thanks for sharing the blog post- it looks very insightful. I will definitely investigate. I am not yet very familiar with Power Query but looking forward to learning more about it so that I may deploy it. I appreciate your help!
Cat-C , glad it helped, good luck with your project.