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

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 do you, I, or anyone else stop the 255 character limit in Excel with detailed explanations.


Download Practice Workbook

Download this practice workbook below.


4 Easy Ways to Stop 255 Character Limit in Excel

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 showing 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 need to append the below text 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 has now 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 do 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 which 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

  • 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.

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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo