Error: "Text values in formulas are limited to 255 characters"

Applies to:

• Velixo Classic
• Velixo NX
• All supported ERPs

Overview

When entering a very long function/formula, Excel displays the following message:

Text of Message

Text values in formulas are limited to 255 characters. To create longer text values in a formula, use the CONCATENATE function or the concatenation operator (&).

Cause

Excel places a limit on the number of characters that can be entered in a formula.

• the number of characters in a formula: 8,192 characters
• the number of characters of an argument of a function: 255 characters

Certain functions such as SI.QUERY() and GI() which include arguments that expect a concatenated list of column names (such as: Column or OutputNames) can easily reach Excel's character limit and cause the error.

Workaround

The simple workaround for this is to move as much information as you can into separate cells and then use cell references within your function/formula.

The above example could be redone as follows:

The below example uses an Si.QUERY() function in Cell A5.  That function references:

• Cell B3 for the Object argument
• Cell B1 for the Filter argument
• the below long string of column names in Cell B2 for the Columns argument

PROJECTID,NAME,DESCRIPTION,PROJECTCATEGORY,PROJECTSTATUS,BEGINDATE,ENDDATE,ACTUALQTY,APPROVEDQTY,REMAININGQTY,MANAGERID,MANAGERCONTACTNAME,RECORD_URL