| Author |
Message |
siguy
Newbie
Joined: 29 Sep 2012
Online Status: Offline
Posts: 10
|
   Topic: Extracting portion of string using delimiters
Posted: 01 Jun 2013 at 11:10am |
|
Hello All,
A bit new to this, but hope my topic/query makes sense.
I was hoping to get some help on extracting and displaying a portion of text in a particular field using delimiters.
The field in question may contain several sentences, but I have been specific in the data stored in this field i.e. each sentence should start with a code like "01A-","01B-" etc. These are all referenced/linked to data in another field (which I've worked out). I want to be able to ONLY extract text that follows a particular code and end at a specified delimiter i.e. "¬".
An example of data in this field {F_TASK.TASK_NOTES} could be;
01A-Walls dirty¬
02D-Lights missing¬
20E-Grills need cleaning¬
At the moment, I use an 'if' statement to tell Crystal that if the first field contains specified text, to link to anything within the {F_TASK.TASK.NOTES} field. However, i only need it to link to its counterpart corresponding to its code and omit everything else.
A swift reply would be very much appreciated, as I am desperate to get this part of the report done by the end of the week, which my director will be expecting to see.
Thanks in advance.
|
 IP Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 03 Jun 2013 at 6:57am |
|
It is not real clear what results you are expecting from the data you have provided. It appears you want to show the remaining text after the code (i.e., Walls Dirty). There is a way to extract that part of the string pretty easy. If all the data shown is in one record. It is still possible to extract the data (a lot more work), it probably will require using an Array. It not clear what you need to do with the extracted string(s).
|
|
IP Logged |
|
siguy
Newbie
Joined: 29 Sep 2012
Online Status: Offline
Posts: 10
|
Posted: 04 Jun 2013 at 12:22am |
|
Thanks for the post kevlray.
Apologies for the ambiguity.
The end result I basically want, for example, is if a value in my first field {QUESTIONS} is ‘01A’, then display only ‘Walls dirty’ from the other field {NOTES}, rather than the whole content of that field.
i.e.
QUESTION | NOTES
01A Walls dirty
Rather than;
QUESTION | NOTES
01A 01A-Walls dirty¬
02D-Lights missing¬
20E-Grills need cleaning¬
Apologies if this isn't as clear as it could be, but please let me know if there is any other information i can give that can aid the process.
Thanks
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 04 Jun 2013 at 7:24am |
|
If the code is always the same length. Try this (two different formulas)
left({F_TASK.TASK.NOTES},3)
mid({F_TASK.TASK.NOTES},5,length({F_TASK.TASK.NOTES})-1)
|
|
IP Logged |
|
siguy
Newbie
Joined: 29 Sep 2012
Online Status: Offline
Posts: 10
|
Posted: 05 Jun 2013 at 1:21am |
Thanks Kev,
Unfortunately the first formula only gives me the code i.e. '01A' and the second formula gives me everything to the right of that.
The second formula is almost what I need, only i need it to cut everything else off after the '¬' symbol or a carriage return. Its a little difficult to define the length of each sentence as the sentence lengths are variable, which is why i wanted to know if there was a formula to identify a stop position i.e. '¬' or carriage return.
so firstly, my formula would need to recognise a start position e.g. '01A', and then display the text after it up to the end position of that sentence i.e. '¬' or a carriage return. This will omit the rest of the data/text that follows it, hopefully looking like this;
Walls Dirty
rather then;
01A-Walls dirty¬
02D-Lights missing¬
20E-Grills need cleaning¬
Thanks
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 05 Jun 2013 at 6:45am |
|
I thought you needed the code so that is why I included that.
The second formula may need some tweaking. I made the assumption that the 01A (or other code is part of the string), thus starting the mid formula at position 5. It appears I did the math wrong for the remaining part of the string. I believe the formula should be mid({F_TASK.TASK.NOTES},5,length({F_TASK.TASK.NOTES})-6. This would take the length of the string minus the first five characters plus one more to get rid of the delimiter.
|
|
IP Logged |
|
siguy
Newbie
Joined: 29 Sep 2012
Online Status: Offline
Posts: 10
|
Posted: 05 Jun 2013 at 9:38am |
|
I haven't implemented this yet, but would this formula consider sentences of varied lengths? Is there not a way to identify a start position and an end position and extract/display anything in between?
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 06 Jun 2013 at 8:38am |
|
if the code is a different length, yes. You could use instr again for the start of the mid string. FYI the previous formula was missing a ending paren. mid({F_TASK.TASK.NOTES},instr({F_TASK.TASK.NOTES},"-"),length({F_TASK.TASK.NOTES})-6).
|
|
IP Logged |
|
siguy
Newbie
Joined: 29 Sep 2012
Online Status: Offline
Posts: 10
|
Posted: 08 Jun 2013 at 10:16am |
Thanks Kev,
Unfortuantely this isnt what i need.
It does identify what the start position is, but not the end position. It seems this formula just chops the last six characters from the end of the string i.e:
01A 01A-Walls dirty¬
02D-Lights missing¬
20E-Grills need cle
I need to have the rest of the sentence truncated from the '¬' or a carriage return.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 10 Jun 2013 at 5:15am |
|
Apparently I am confused on how the original data looks like. Is '01A-Walls Dirty' one field or multiple fields?
Thanks.
|
|
IP Logged |
|
|
|
