Splitting Data out of a Field

[LDickenson]

Hi

I have one field i.e. Table.Notes

This field has many entries against each record and the notes are pre-fixed i.e.


RECORD NO NOTE FIELD
Record 1 PREFIXED WITH NoteA: sdkfskjfsldflksd
PREFIXED WITH NoteB: dosdofposdf;lksf;ksfdk;
PREFIXED WITH NoteC: kdsflsdkflskflsdf
PREFIXED WITH NoteA: lskdflksdfljsd

What I need to do is a formula so I can split the notes out so that I will have in my report is:-

Record No NoteA NoteB NoteC
1 sdkfskjfsldflksd NoteB: dosdofposdf;lksf;ksfdk; kdsflsdkflskflsdf


To give the impression that the notes are separate fields rather than data held in one field and just pre-fixed.

Any assistance appreciated.

Thanks,

Lisa



1

 

[hilfy]

What version of Crystal are you using?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[fisheromacse]

there may be a better way, but IF the Notes are always going to be in the same order (ie: NoteA, NoteB, NoteC) i would use extractstring, and create a formula for each note value.

\\{@ExtNoteA}
extractstring({table.field},"NoteA:", "NoteB

\\{@ExtNoteB}
extractstring({table.field},"NoteB:", "NoteC

\\{@ExtNoteC}
numbervar extC := instr({table.field},"NoteC:");
RIGHT({table.field},extC+6)

If the notes could be in any order, then it gets a bit more complicated. You need to find the location of each NoteX and then pull the value after it.

\\{@NoteA}
numbervar lnt := len({table.field};
numbervar noa := instr({table.field},"NoteA:");
stringvar nta := mid({table.field},noa+6, lnt);
numbervar noaend;
IF instr(nta,"Note")<>0 then noaend := instr(nta,"Note") else noaend := lnt;
LEFT(nta,noend)

\\{@NoteB}
numbervar lnt := len({table.field};
numbervar nob := instr({table.field},"NoteB:");
stringvar ntb := mid({table.field},nob+6, lnt);
numbervar nobend;
IF instr(ntb,"Note")<>0 then nobend := instr(ntb,"Note") else nobend := lnt;
LEFT(ntb,nobend)

\\{@NoteC}
numbervar lnt := len({table.field};
numbervar noc := instr({table.field},"NoteC:");
stringvar ntc := mid({table.field},noc+6, lnt);
numbervar nocend;
IF instr(ntc,"Note")<>0 then nocend := instr(ntc,"Note") else nocend := lnt;
LEFT(ntc,nocend)


my usual disclaimer applies...i do not have crystal in front of me so am working off of an every increasingly faulty memory and apologize in advance for any errors in logic, typos or other mistakes that may make this more difficult than it has to be.