Another Solution for 1024 Character per Cell Display Problem

Remember back in January when Mike requested a solution for Excel's (pre-Excel 12) 1,024 character per cell display problem? I worked something up for him, but as part of the solution, I inserted new rows and merged cells. Another person let me know that this caused a problem if the cell was part of a table that one wanted to sort.

So...I went to work on a better solution, and I think what I've produced is far superior, though could still be taken further.

Essentially, what I've written checks each cell in a selection to see if it has more than 1024 characters. If it does, it then automatically places line feed characters -- chr(10) in VB -- at the end of each line based upon an approximate number of characters that should fit on each line (only in Arial 10 regular font). It was a bit tricky figuring out how to determine the end of each line -- it could be looking for a space near the approximate end based upon the width of the cell, or a line feed character somewhere before that point.

In the end, it works well. Some possibilities for enhancements? Well, it could be improved to work with any size, name, and style of font. This one converts affected cells to Arial 10 Regular font, which is typically the default font for Excel anyway.

I think those of you who've signed up for my Excel_Geek Insiders service will enjoy it. Just let me know if you need help "strapping" the code on to an existing file you've got. If you haven't already subscribed, you might think about doing so. It's only $39.95 per year, and you get copies of all the file I produce for $50 Project requestors, as well as a few other projects, like this one, that I simply feel like doing.

Interested? Sign up today.

Work Around for 1024 Character Per Cell Display Problem

Here's a recent $50 Project Request that I got from Mike:

Mike was going to use Excel to log transcripts of videos, but he kept running into Excel quirky little 1,024 character display issue. Everyone's probably aware of the current versions of Excel's 1,024 character per cell display problem, right? If you aren't, here's the jist: In Excel '97, '00, '02 and '03, a cell can contain up to 32,767 characters. However, if a cell contains more than 1,024 characters, the following rules apply:

• Characters after approximately the 1,024th character do not appear in the cell; however, they appear in the formula bar when you edit or select the cell.


• Although characters after approximately the 1,024th character do not appear, you can detect and manipulate them with worksheet functions (for example, the RIGHT and MID functions) and macro commands (for example the Characters property).


• If you copy a cell that contains more than 1,024 characters, and then paste them in another cell, all of the characters are pasted into the new cell. However, characters after approximately the 1,024th character do not appear in the destination cell.


• Characters in a cell after approximately the 1,024th character are not printed and may not appear in print preview.


• You may be able to see more than 1,024 characters by increasing the row height and column width of the worksheet, by modifying the display settings of the system, and/or by inserting strategically place hard character returns (ALT+ENTER).

Here's a nice Microsoft article on the topic, if you're interested.

Now, Excel 12 will not have this issue, but Mike likely didn't want to wait for its release then immediately shell out the bucks to upgrade everything, so I set out to write some code that would patch together a clever work around to this problem.

What I basically did was write a macro that would check each cell in the column containing the transcript texts (which are the ones regularly pusing the limit) to see if any of them contained more than 1,024 characters. If one did, I'd add a new row below that one, truncate the long text at 1,024 characters, and place the remainder in the new cell directly below the first one (and repeat if necessary). Then I'd merge the top and bottom cells in all the other columns and format the cells containing the long text so that they visually seemed to be one cell.

Pretty crude, but works great. An intersting note, the .text property of a cell containing the long text only returns the first 1,024 characters, but the .value property returns the actual contents of the cell.

What did Mike end up doing in the end? Well...unfortunately, he decided to use a different program for logging called LLama Log that interfaces with Final Cut Pro quite well and has added some added features that he couldn't readily get in Excel.

Oh well. If anyone else out there wants the project...it's only $50. But of course Excel_Geek Insiders subscribers get all my projects for only $39.95 per year. Sign up today!

Excel_Geek