Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Ontario, Canada
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting results to values (Excel 2000)

    Hi,

    I need to convert the result of one particular formula to values. I use a formual like this: =INDEX(rawdata,MATCH($C$5,LocCode,0),9)
    The problem is with this field the source data has more than 256 characters and the result only shows some of it. To validate this I checked using the LEN() and compared the source and result. The result field shows fewer characters but when I use copy/paste/values from the result and paste it somewhere elese, then all the characters show up. I know the formula captures it but only displays some of it due to Excel's limitation I guess.

    I have attached a sample..see the 'Form Tab'.


    Thanks,
    Jay.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Converting results to values (Excel 2000)

    The problem is not 256 chars but a limation in XL that it will only display in a cell the first ca 1000 chars in a cell. This will be the same for a formula (as you have) or for copying the contents.

    One solution would be to break up your directions into smaller segments (of <1000 char paragraphs) and then make a cell for each 1 individually

    Another would be to use a textbox.
    create a name (insert - name define)
    name:directions
    refersto:=INDEX(rawdata,MATCH($C$5,LocCode,0),9)
    Then instead of using a cell, add a textbox from the control toolbox.
    Change the CellLink property to "Directions" (no quotes)
    and WordWrap and Multiline properties to "True"

    This should do what you want.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Converting results to values (Excel 2000)

    A cell never displays more than 1024 characters, so even if you copy and paste, the user will not be able to see all the text, except by looking at the formula bar. See attached version (it uses code in the Worksheet_Change event.)
    Perhaps you could split the directions over two or three cells.

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Ontario, Canada
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting results to values (Excel 2000)

    Steve/Hans

    Thanks for the responses. The breaking up of the cell I thought of but that was not the route I wanted to go. However, Steve your alternate solution is amazing! I've been wowed. Great stuff !

    Jay.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •