XL: Counting the Number of Characters in a Text Box

• 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()

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