XL: Counting the Number of Characters in a Text Box

Last reviewed: February 2, 1998
Article ID: Q95628
The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0
  • Microsoft Excel for OS/2, versions 3.0

SUMMARY

In Microsoft Excel, you can count the number of characters in a text box by using the TEXT.BOX function in a macro.

MORE INFORMATION

An undocumented feature of the TEXT.BOX function is that it returns the number of characters in the text box. Carriage returns are also counted as characters. The following macro will display the number of characters in a selected text box in an Alert dialog box.

The macro is as follows (starting in cell A2 of the macro sheet):

   A2: =SELECTION()
   A3: =TEXT.BOX("",A2,11000)
   A4: =ALERT("There are "&A3&" characters in "&A2)
   A5: =RETURN()

NOTE: There is no space between the quotation marks in line A3.

The formula in cell A2 above, returns the name of the selected text box to cell A2 on the macro sheet.

Cell A3 uses the TEXT.BOX function to add a null after the last character in your text box. Nothing gets added to the text box; however, the number of characters in the text box will be returned to cell A3. The number 11000 can be changed to any large number as long as it is larger than the total number of characters that will be in the text box. The maximum size allowed for a text box is 10,240 characters so 11000 is used because this number will always be larger than the number of characters in the text box.

The formula in cell A4 uses the ALERT Function to display a dialog box that displays how many characters are in the selected text box.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 282-283 "Microsoft Excel Function Reference," version 4.0, page 432


Additional query words: 5.00 3.00 4.00 4.00a limit
Keywords : kbmacro
Version : WINDOWS: 3.0, 4.0, 4.0a, 5.0; MACINTOSH: 3.0, 4.0, 5.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbhowto


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: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.