Forum Discussion

Cat-C's avatar
Cat-C
Copper Contributor
Jul 19, 2021
Solved

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

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 char...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 20, 2021

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

Cat-C's avatar
Cat-C
Copper Contributor
Jul 19, 2021
Thanks, Sergei!
Cat-C's avatar
Cat-C
Copper Contributor
Jul 19, 2021
Is there another way to evaluate a string of text that exceeds that character number?

Thanks again for your help.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 19, 2021

    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

    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.

    • Cat-C's avatar
      Cat-C
      Copper Contributor
      Jul 19, 2021
      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.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 19, 2021

        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.

Resources