Jul 19 2021 12:56 PM
Jul 19 2021 12:56 PM
Hi, I am using the following formula to add together long text strings in a worksheet:
However, the formula cuts off the text being added together after it hits a certain number of characters in the cell. I used the =LEN(A1) formula to determine that the total number I am able to add together before it cuts off is 32, 767. Is there a setting that I need to change, or something else I can do to get this formula to work? I also notice that when I entering 3 or 4 of the calculations manually in a column, highlight them, and attempt copying the formula down to apply the formula to the entire data set, it seems to cut off after 70-80 rows.
Jul 19 2021 01:00 PM
You can do nothing with that, Excel limit is
Jul 19 2021 01:07 PM
Jul 19 2021 01:45 PM
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.
Jul 19 2021 01:54 PM
Jul 19 2021 02:19 PM
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.
Jul 20 2021 05:39 AM
@Sergei Baklan 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!
Jul 20 2021 08:31 AMSolution
@Cat-C , glad it helped, good luck with your project.