[Solved] Any way to get around 256 character limit?

  • Hello,


    I understand each cell in Excel has a 256 character limit. Beyond that, the cell displays '#######'. Is there any way to get around this? I have a macro thanks to this forum, which pulls all my info to a text file, but whenever the cell exceeds the limit, although it shows the text in the Formula bar above the spreadsheet, the cell is imported as '#####'. Anyone have an issue like this before?

  • hi surplusbc


    how abt double clicking the gap between the column headers to widen the column?


    ie. if your lengthy text is in A1, go to between A & B and double click


    or you can go to format>alignment >click wrap text to better display yr text


    Not sure if this is what you need


    cheers
    xlite:wink1:

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • If you are using Excel 97 or later, the 256 character limit has increased substantially. The "####" display indicates that your columns are not wide enough (or the font is too big) to display with current settings. Formating as elite suggests, and possibly changing to a different and/or smaller font) should solve this.

  • Thanks guys... I am in Excel 97 SR-2, and I am getting a 256 character limit. I've tried merging cells, smaller fonts, increasing cell size and I'm still getting the '######'. At first I thought it was an issue of my cell size, as stated in your post, but I just counted out 256 characters and on the 257th, it went to the pound signs.

  • err in that case maybe you want to divide the cell into 2?


    if it is in A1,


    A2=LEFT(A1,CEILING(LEN(A1)/2,1))


    A3=RIGHT(A1,FLOOR(LEN(A1)/2,1))


    usually i would manually divide the text into two though


    cheers
    xlite:spin:

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Strange. I've been testing with Excel97 SR1 and do not find this problem. It displays 256+ characters without problem.


    Is your cell content entered as text, or is it the result of a formula? This might make a difference.


    Also, what is your cell format setting? (FORMAT > CELLS > NUMBER > ???)

  • Hmmm...


    From the xl help file on 'specifications':


    Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.


    Column width 255 characters


    And the help file on 'Correct a ##### error'
    Occurs when a column is not wide enough, or a negative date or time is used.


    Review the possible causes and solutions.


    Possible causes and solutions
    Column is not wide enough to display the content


    Increase the width of the column Select the column, point to Column on the Format menu, click Width, and then enter a number.


    Shrink the contents to fit the column Select the column, then on the Format menu, click Cells, click the Alignment tab, and then select the Shrink to fit check box.


    Apply a different number format In some cases, you can change the cell's number format to make the number fit within the existing cell width. For example, decrease the number of decimal places after the decimal point.


    Dates and times are negative numbers


    If you are using the 1900 date system, dates and times in Microsoft Excel must be positive values.


    When you subtract dates and times, make sure you build the formula correctly.


    If the formula is correct, although the result is a negative value, you can display the value by formatting the cell with a format that is not a date or time format. Click Cells on the Format menu, click the Number tab, and then select a format that is not a date or time format.


    Any help?

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • Okay, I stopped working on the issue and was doing something else in Excel. Then I noticed, I was blowing away the 256 character limit. I went in to my existing spreadsheet and just copied the cell to a new spreadsheet and it worked. There must be a setting in that spreadsheet that I am not noticing. Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!