SOLVED

Combining Text Strings with Ampersand (=a1&" "&a2 formula) cuts off after 32,767 characters

Occasional Contributor

Hi, I am using the following formula to add together long text strings in a worksheet:

=A1&" "&A2 

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.  

 

9 Replies
Is there another way to evaluate a string of text that exceeds that character number?

Thanks again for your help.

@Cat-C 

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

image.png

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.

Thanks for your help and suggestion, Sergei. The goal is to evaluate # instances words appear across the entire grouping of text strings to look for patterns- so hoping to group them all together, then use Text to Columns to separate the words from each other, then turn the words into a pivot table so the instances of words can be counted.

@Cat-C 

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.

@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!

 

best response confirmed by Cat-C (Occasional Contributor)
Solution

@Cat-C , glad it helped, good luck with your project.