Extract part of a string based on a variable set..
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22089
Printed Date: 02 May 2024 at 5:22pm
Topic: Extract part of a string based on a variable set..
Posted By: aaron80126
Subject: Extract part of a string based on a variable set..
Date Posted: 16 Sep 2016 at 11:08am
|
I'm requesting help to accomplish the extraction of a string based on the discovery of a variable, using Crystal Syntax.
My report contains part #s, shown in the left column: 45567P1 P1 89933-01 -01 1R456X1 X1 B8821C621R101 R101 G46-311 -311 I need to display a certain section of the part numbers which are shown in the right column above (P1, -01, etc.). The extraction comes from the right side of the part number. So the logic is, reading each part # right to left, to display everything to the right of the first alpha character or "-" encountered. In the case of "-" the dash needs to be displayed. I'd greatly appreciate the assistance in getting this done. I'm just not advanced enough yet in CR to figure this out . . . though I sure have tried. - Aaron in Englewood Colorado ------------- aaron80126 |
Replies:
Posted By: kevlray
Date Posted: 19 Sep 2016 at 4:36am
|
I am thinking something like this. It assumes that there will be a dash or alpha character. right({partno},revinstr({partno},not(isnumeric({partno})))+1) I have not tested this code. |
Posted By: aaron80126
Date Posted: 19 Sep 2016 at 7:23am
|
Thank you for your response. I tried the formula you sent (modified for my table/field name):
right({OrderDet.PartNo}, revinstr({OrderDet.PartNo}), not(isnumeric({OrderDet.PartNo})))+1) but I get the error message: "A number, currency amount, boolean, date, time, date-time, or string is expected here." (revinstr is highlighted). I tried the formula in both formula workshop and select expert but same error message. Any idea what could be causing this? - Aaron ------------- aaron80126 |
Posted By: aaron80126
Date Posted: 19 Sep 2016 at 8:20am
|
A little more info on this issue:
I tried InstrRev instead of RevInstr and then my error changes to: "The remaining text does not appear to be part of the formula." (with the last ")" on the right end of the formula highlighted.) Also, I'm trying to run the formula from "Formula Workshop - Format Formula Editor - Display String" though I get the same message when I try to run it in Field Explorer - Formula Fields. - Aaron ------------- aaron80126 |
Posted By: kevlray
Date Posted: 19 Sep 2016 at 12:01pm
|
I got the parans match correctly now (thats what I get for just trying to remember function names). right({OrderDet.PartNo}, revinstr({OrderDet.PartNo}, not(isnumeric({OrderDet.PartNo})))+1) |
Posted By: aaron80126
Date Posted: 20 Sep 2016 at 4:12am
|
Thanks for the updated formula . . .
However, the message I get when using "revinstr" is "A number, currency amount, boolean, date, time, date-time, or string is expected here" and "revinstr" is highlighted. When I change "revinstr" to "InstrRev" I get the message "A string is required here" and "not(isnumeric({OrderDet.PartNo}))" is highlighted. Any other ideas on how I can get this to fly, please? BTW, using CR XI here. - Aaron ------------- aaron80126 |
Posted By: kevlray
Date Posted: 20 Sep 2016 at 8:21am
|
I need to re-think this (I really need more sleep). Basically I have to search from the end of the string until I hit a non-numeric (loop?), then only strip off that part of the string. For some reason I was trying to use InstrRev, but a alpha characters is throwing me off. |
Posted By: aaron80126
Date Posted: 20 Sep 2016 at 8:38am
|
I get the sleep (lack of) part . . . I'm grateful for your efforts on this and yes, your assumption that the formula is searching for the first non-numeric character going from right to left is correct. - Aaron ------------- aaron80126 |
Posted By: kevlray
Date Posted: 20 Sep 2016 at 1:26pm
|
The following code does work (I actually tested it :-) Just replace thefield with your field. numbervar i :=len(thefield); while isnumeric(mid(thefield,i,1)) do ( i := i -1); mid(thefield,i) |
Posted By: aaron80126
Date Posted: 21 Sep 2016 at 3:43am
|
This works perfectly! Thank you very much for lending your expertise.
I have no idea what much of this syntax means so I'll be dissecting it for a better understanding. Great job! - Aaron  ------------- aaron80126 |
Posted By: Frank in VA
Date Posted: 23 Feb 2017 at 4:23am
|
Hi Kevlray, I have a need to do something which I believe is similar to what you've created here. I have a variable sized field which ends with an account number. I need to report on all records where the account number begins with an '8', which will always be in the 10th position from the right. Is there a way, starting from the end of the field, to check the 10th position to see if it is an '8'? I've been playing around with the right() function but not getting the results I need. Thanks! Frank ------------- Frank |
Posted By: kevlray
Date Posted: 27 Feb 2017 at 5:28am
| Right is the way to go, but you have to use the length of the string -10 (i.e., right({somestring},len(somestring)-10) ) |
Posted By: Frank in VA
Date Posted: 28 Feb 2017 at 8:54am
|
Awesome! Thanks!
Frank ------------- Frank |