I have a request to split a description field into two lines for display purposes. This is a financial application, where the report is sorted by security name. If the security is a bond, then the user would like the interest rate and maturity date to be displayed under the security name.

I came up with a solution, where I use match, to first print out all the security names, with a second match file, containing the interest rate and maturity dates, for those securities that are bonds.

This accomplished the goal of producing a reported sorted by security name, with a second line being produced showing interest rates and maturity dates for bonds.

This solution works great with PDF files. The problem is that the user also wants the report in EXCEL format. When I output the report to EXCEL, the second line of the security name (containing the interest rate and maturity date for bonds) prints in it's own cell, below the security name.

This makes it impossible for the user to sort the report on security name. What I would like to do is produce a single alpha field, that can be output into an EXCEL spreadsheet, that would split up the text into two lines within a single cell. I thought that maybe I could embed a line feed character within the alpha field, but I do not know if that is possible.

Is this possible?

This message has been edited. Last edited by: <Kathryn Henning>,

The best idea I can come up with is to conditionally apply that splitting of the description field.

I tried a few things in excel, but even if you use the unicode character for the linefeed, it will still put the "new line" on the second row in Excel ('97).

/edit: I found I had to clarify this a bit. I copy/pasted the characters from a unicode file in Excel with the option "Paste Special � as Unicode".
Afterwards I actually tried creating unicode characters as DEFINE fields, but you'd get the same result as with the <br> html tag:

A
B

).

John, this works in html, in Excel the linefeed char is Alt+Enter, whatever that works out as.

DEFINE FILE CAR
ALPHA/A50 = CAR | '<br>' | 'LINE 2';
END

I tried doing a <br> concatenation as well, but you'll get something like this in excel:

   A                   B<br />1 split1 <br> split2<br />2

On IBI's website at:http://techsupport.informationbuilders.com/sps/82291012.html

They have an example of what they claim is a method of spliting a single EXCEL cell into two lines. The sample code is:

DEFINE FILE CAR
ABC1/A5 = EDIT (COUNTRY, '99999');
ABC2/A10 = EDIT (COUNTRY, '$$$$$9999999999');
ABC3/A20 = ABC1 || '<BR>' || ABC2 ;
END

TABLE FILE CAR
PRINT
ABC3
ON TABLE PCHOLD FORMAT EXL2K
END

They claim this works, but when I tried it it didn't work. When the output is directed to EXCEL, the webfocus engine decides to convert the '<' to 'amper LT' and converts the '>' to 'amper LT'. This prevents EXCEL from printing the output on two lines. When the output is directed to HTML, the webfocus engine leaves the <BR> as is, and HTML correctly splits the single cell, into two lines.

I wish there was a method, to have webfocus output the data field into EXCEL, and not convert the '<' and '>' characters into there ampersand equivilents.

IBI tech support came up with a solution on how to embed a line feed within an EXCEL cell. Here is the sample code:

-* File xxxx.fex
-* File XLS_Example.fex
DEFINE FILE CAR
CR/A1 = HEXBYT(13, 'A1');
LF/A1 = HEXBYT(10, 'A1');
CRLF/A2 = CR || LF;
ABC1/A5 = EDIT (COUNTRY, '99999');
ABC2/A10 = EDIT (COUNTRY, '$$$$$9999999999');
ABC3/A30 = ABC1 || CRLF || ABC2 ;
END

TABLE FILE CAR
PRINT
ABC3
ON TABLE PCHOLD FORMAT EXL2K
END

Thanks for the update,
There is nothing worse than not ending a thread on a good note.

Thanks JohnK,
I got the same issue and your solution totally works for me. Thanks a lot.

For Excel all you need is the LF. You don't need the CR.