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:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Comments for this tip:

matt    23 Mar 2015, 21:33
Correction: a cell only shows 255 characters when VBA is used to enter the cell contents.
matt    23 Mar 2015, 21:05
This is wrong for Excel 2003. Excel 2003 only displays 255 characters. After that it just shows hashes.
Mark P. Kessinger    16 Mar 2015, 19:16
Unless there is a particular reason the data must remain in Excel, a far better workaround than anything I've seen suggested here would be to copy and paste the data into a table in Word, which has no such limitations. To do so, select the entire range of cells potentially containing data,then press ctrl-c to copy it. Then open a new word document. (If the table is very wide, you may want to set the page orientation in the word document to landscape, and or change the paper size to something wider, such as legal.) Now hit ctrl-v to paste the Excel data into the Word document; it should paste as a Word table. (If the table exceeds the page/margin width, simply go into the table properties, clear any specified column widths, and set the table size to 100%.)

Remember that Excel is not really designed or intended to be a tool for handling large chunks of text. If that is what you need to do, a good word processor, as Microsoft Word, is nearly always a better choice.
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 © 2015 Sharon Parq Associates, Inc.