How Do I Stop 255 Character Limit in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

During the many calculations phase, we encounter that text in cell formulas needs to be under 255 characters, which is annoying. In most cases, we have no option to shorten the text at all. If you are curious to know how you can stop the 255-character limit in Excel, then this article may come in handy for you. In this article, we discuss how you, I, or anyone else stop the 255-character limit in Excel with detailed explanations.


Stop 255 Character Limit in Excel: 4 Easy Ways

For the demonstration purpose, we used two separate long texts which can show errors when we used them directly. We presented four ways in which you can resolve the issue.


1. Using CONCATENATE Function

We can use the CONCATENATE function, we can join two long texts (over 255 characters) altogether without any warning sign.

Steps

  • If we need to use a formula that involves a character length greater than 255, we will face a warning like the one shown below.
  • Because Excel put a restriction of 255 lengths on the character length in Excel.
  • The warning box states that you need to use the CONCATENATE function or the Ampersand operator.
  • Click OK after this.

  • Let us append the text below altogether.
  • Now, we will separate the text into two separate parts, and then we will add them together using the CONCATENATE function.
  • The key here is to separate the text into almost equal parts in cells C4 and in C5.

Using CONCATENATE Function to Stop 255 Character Limit in Excel

  • Select cell C7 and enter the following formula:

=CONCATENATE(C4,C5)

  • Doing this will append both parts of the text altogether.
  • And this time there is no warning sign to be seen.

Using CONCATENATE Function to Stop 255 Character Limit in Excel

  • And this is how you, I, or anyone else can stop the 255-character limit in Excel using the CONCATENATE function.


2. Using Ampersand Operator

We are using the Ampersand sign to join two long texts of over 255 characters placed in two separate cells.

Steps

  • Similar to the CONCATENATE function, we can easily add different parts altogether using the &(Ampersand) sign.
  • But if we tried to add text longer than 255 characters directly in the formula bar, then there would be an error.
  • And this error clearly instructs us to opt for the CONCATENATE function or the & sign in between text.

  • To do this, we need to separate the texts into two different parts.
  • Then, we will use the cell location of the texts to add them together.

Using Ampersand Operator to Stop 255 Character Limit in Excel

  • Select cell C7 and enter the following formula

=C4&C5

  • Entering this formula will add the text in two cells altogether in cell C7.

  • After entering the formula, you will notice that cell C7 now has the appended part of the text in cells C4 and C5.
  • And now it’s not showing any warning signs right now.

  • And this is how you, I, or anyone else can stop the 255-character limit in Excel using the Ampersand sign.

3. Save Your Excel File as XLSX Format

XLSX files actually store data in compressed B format.  In an XLS file, data is stored as binary streams in a compound file, as explained in [MS-XLS]. An XLSX file, on the other hand, is based on the Office Open XML format, which stores data in ZIP files that are made up of compressed XML files. If you unzip the.xlsx file, you can determine the structure and files underneath. The XLS file has some limitations compared to the XLSX version. XLS normally doesn’t have the capability of showing over 255 characters. XLSX does have the capability of showing strings having over 255 characters.

So, if your cell contains more than 255 characters and your Excel file format is XLS, try switching to XLSX. This may resolve the issue.


4. Modifying Registry Editor

Registry Editor is a visual operator that allows users to modify the configuration settings of various programs. Altering those settings can enable us to avoid the issue of the 255 character limit in Excel efficiently.

Steps

  • If your imported data in Excel is being truncated below 255 characters automatically, then the below procedure can help you avoid it.
  • In the beginning, type Registry Edit in the search box.

Modifying Registry Editor to Stop 255 Character Limit in Excel

💬 Note

  • This problem is specifically designated for the user of Myssql data importer as the user of Myssql often finds their data to get truncated while importing. Also, this problem normally exists in the Excel version 2007-2013.
  • Then, in the Registry Edit box, type the below address in the registry editor address bar and press enter.

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel

  • After pressing enter, you will be taken to the following location.
  • In that location, double-click on the TypeGuessRows.

Modifying Registry Editor to Stop 255 Character Limit in Excel

💬 Note

  • If your windows version on your computer is 32-bit, then you need to remove the WOW6432Node part in the address.
  • Then in the Edit DWORD (32-bit) Value box, select Decimal in the Base.
  • And put 0 in the Value data.
  • Click OK after this.

TypeGuessRows.

  • After clicking OK, the issue should be resolved.

Download Practice Workbook

Download this practice workbook below.


Conclusion

To sum it up, the issue of how you can stop the 255 character limit in Excel is discussed in 4 different ways with elaborate descriptions.

For this problem, a macro-enabled workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.


<< Go Back to Characters | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

2 Comments
  1. i did the -4. Modifying Registry Editor-. the message can’t do more than 255 still comes up
    i have win 10 and rxcel 2021

    thank you

    • Reply Avatar photo
      Rubayed Razib Suprov Jul 24, 2023 at 1:09 PM

      Greetings Gin Got,
      Sorry for the issue that you are facing. Unfortunately, the issue of 255 characters in a single cell is a fundamental feature in the Excel environment. But on the other hand, the character limit in a formula is infinite. So I suggest you stick to this article’s first or second method.
      Now coming to the 4th method in which you are not getting so much success, is that this method is only for Users of Excel 2007-Excel 2013 in a very specific area. Users who are getting trouble importing data to the Myssql server are experiencing this issue of getting characters truncated in the cell. This 4th method is the solution for that reason. The article will now be updated with a note with this disclaimer. If you need further assistance then please feel free to contact us again, attaching necessary files is recommended.

      Thanks and Regads
      Rubayed Razib

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo