Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Character Limits for Cells

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Character Limits for Cells.

Carolyn describes a situation in which a coworker has a worksheet with large amounts of text in several cells. A few cells will not display all the text even with text wrapping and a smaller font selected.

This is to be expected, because in Excel there are two separate limitations at play: a limit on what can be entered in a cell and a limit on what can be displayed. In most cases, the limit on what can be entered in a cell is not a real issue; Excel allows you to enter up to 32,767 characters in each cell. All of these characters will show up in the Formula bar just fine.

The problem comes with the display limitation. There is a limit that Excel will display only the first 1,024 characters in each cell. In other words, if there is anything more than this in a cell (which could be likely in some circumstances), then it won't display; Excel pretends like it isn't even there. You can't get around this limit by changing fonts, cell sizes, wrapping status, or anything else.

There are a couple of ways that you might find acceptable as workarounds. You could, for instance, insert the lengthy text selections into text boxes rather than into cells. The text boxes don't have the same display limit, and you can format the contents in any way desired.

Another approach is to actually add the long information to a Word document, copy it, and then paste it into Excel (using Paste Special) as a Word object. You'll need to play with the formatting to make sure the text appears as you want, but this may suffice.

Both of these approaches involve getting the text out of the cells and placing it in a different object that can handle the longer text. A different approach is to simply upgrade your version of Excel. In later versions of Excel (after Excel 2003) Microsoft changed the display limits to match those of the cell entry limits. In other words, you can enter and display up to 32,767 characters in later versions of Excel.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3163) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Character Limits for Cells.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

 

Comments for this tip:

Kary    08 Oct 2014, 15:03
Excel 2010 still only displays 1,024 characters.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Kary    08 Oct 2014, 15:03
Excel 2010 still only displays 1,024 characters.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Kary    08 Oct 2014, 15:03
Excel 2010 still only displays 1,024 characters.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Kary    08 Oct 2014, 15:03
Excel 2010 still only displays 1,024 characters.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Kary    08 Oct 2014, 15:03
Excel 2010 still only displays 1,024 characters.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Kary    08 Oct 2014, 15:02
Excel 2010 still only displays 1,024 characters.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Kary    08 Oct 2014, 15:02
Excel 2010 still only displays 1,024 characters.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Kary    08 Oct 2014, 15:02
Excel 2010 still only displays 1,024 characters.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Anthonyk    13 Jul 2014, 07:22
I have heard about these kind of issues before a lot over the internet and through colleagues, but then came a day when I found out about LongPathTool, this software was a lifesaver and I would recommend everyone to give it a try, go ahead it's Long Path Tool - it's the best.
Ulises     10 Jun 2014, 19:25
Is there a way of determining how many characters are visible in a group of merged cells? What I see on the display is different than what is printed? I need to continue the content that is in one group of merged cells into another group of merged cells so that the information flows (i.e., one group merged cells is on the page 1 and the second group is on page 2). When I use the Len function to try to figure this out it gives me different values for what is visible on the print preview page, causing me to manually need to copy and past. Help!
maryc    28 May 2013, 18:36
My cell only contains 19 characters and has plenty of room, but will not print the last letter. (Excel 2010)
Dom    07 Feb 2013, 11:35
Would the display limits be increased if the cells were merged? For example, if two cells were merged, can one merged cell hold 65,534 characters (32,767 x 2)? Thanks.
awyatt    10 Mar 2012, 09:02
Yes, this can be done using macros and (perhaps) data validation.
David    08 Mar 2012, 20:59
Is there a way to limit the amount of characters that someone enters into a cell?

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 4+5 (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.