Sounds like the easiest way would be to extract fld3 as a multi-line field then create 5 (if 5 is the maxium number of lines possible) calculated fields and SPLIT (LSPLIT or RSPLIT) the extracted field into those.
That said the contents of the lines may make the split a little tricky but if they are 3 character codes as per the sample it should be quite straightforward.
Hide the original fld3 field for the final output.
If there is more to it than that let us know.
I am exporting to an excel document.
Report example is as follows:
fld1 -- fld2 -- fld3 -- fld4 -- fld5
222 -- descr - 555 -- aaa -- bbb
Fld3 has a variable number of lines (anywhere from 1 to 5, and sometimes it is blank) - I want each line of fld3 to translate to a separate column in excel with all the other fields.
(fld1-- fld2 -- fld4-- fld5-- fld3ln1-- fld3ln2 fldln3...)
222 - descr-- aaa--- bbb
- 555- 435--- 482
It kind of works if my detail line contains multiple lines, but not quite. I've tried making fld3 my detail. Couldn't get it to work right, either. I believe I've done this before in an earlier version. (I'm on V7 now.)
(Basically, extracting an address block does the same thing I want to do, only without addresses.)
I hope someone can help.
OK Fields in blocks of 5 characters separated by spaces should be no problem, just use the space as the separator character. There is just a chance that you may end up with a gap between 2 numbers which is of two or more spaces and that can throw the results, mainly because you may not spot the double space. If that happens you need to use the TRIM or LTRIM functions perhaps.
would give the first part of the 5 'line' field.
would give the second and so on.