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

Method 1 – Using CONCATENATE Function

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 character lengths.
• The warning box states that you need to use the CONCATENATE function or the Ampersand operator.
• Click OK.

• Separate the text into two separate parts, and 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.

• Select cell C7 and enter the following formula:

• This will append both parts of the text altogether.

Method 2 – Using Ampersand Operator

Steps:

• 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 instructs us to opt for the CONCATENATE function or the & sign in between text.

• We need to separate the texts into two different parts.
• We will use the cell location of the texts to add them together.

• Select cell C7 and enter the following formula

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

• Cell C7 now has the appended part of the text in cells C4 and C5.

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

Method 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:

• Enter Registry Edit in the search box.

• In the Registry Edit box, enter the following address.

• Press Enter to be take to the following location.
• Double-click on the TypeGuessRows.

• In the Edit DWORD (32-bit) Value box, select Decimal in the Base.
• Put 0 in the Value data.
• Click OK.

Download Practice Workbook

<< Go Back to Learn Excel