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