Microsoft Knowledge Base

XL: LEN() Function Truncates Count at 255 Characters
Last reviewed: March 27, 1997
Article ID: Q118979
5.00 5.00c 7.00 | 5.00 
WINDOWS         | MACINTOSH
kbprg kbcode

The information in this article applies to:

  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for the Macintosh, version 5.0

SUMMARY

When you use the Visual Basic, Applications Edition, Len function to determine the number of characters in a text box, you may receive a count of 255 even if the number of characters in the string is greater than 255.

WORKAROUND

A better method for counting characters in an object than using the Len function is to use the Characters method and to use the Count property. To return the correct count for the number of characters in a text box, use the following Visual Basic statement: 

   Sub Character_Count()
        MsgBox Worksheets("Sheet1").TextBoxes("Text 1").Characters.Count
   End Sub
Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

MORE INFORMATION

The Len function returns the number of characters in a string. The function can be used to return the count of the number of characters in an object such as a text box. However, the Len function will count only a maximum of 255 characters (including carriage returns).

Example

The following Visual Basic statement will return a message box displaying the number of characters for the text box named with the object ID text 1 on Sheet1 but, if the text exceeds 255, the Len function truncates the text count at 255 characters. 

   Sub Len_Count()
      MsgBox Len(Worksheets("Sheet1").TextBoxes("Text 1").Text)
   End Sub

KBCategory: kbprg
KBSubcategory: kbcode

Additional reference words: 5.00 5.00c 7.00 text-box
Keywords : kbcode kbprg
Version : 5.00 5.00c 7.00 | 5.00
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 27, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.