Solved

extract text at end of field

Posted on 2013-11-26
4
488 Views
Last Modified: 2014-08-05
How would I use the left + len + InStrRev functions to start at the end of the field and find "$bill" and then return that text pluss the remaining string?  I know how to use the right function to bring back the last few characters, but the issue is that the string after "$bill" varies. so I want to pull "$bill" and everything after that.  

It would also be nice to have it return something like "***" if the remaining string was longer than X characters (ie 10 characters), as a way to indicate that the "$bill" code was entered wrong....this would tell me that either the person entered too many characters after "$bill".  Alternate suggestions/approaches are welcome.

i.e. - the memo field would contain a string similar to below:

90 day EWP
five year, to be billed biennial. maturity date 11/13/18
1st 11/13/13
2nd 11/13/14
3rd 11/13/15
4th 11/13/16
5th 11/13/17
$bill52.2.1
0
Comment
Question by:IO_Dork
  • 2
4 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39678986
Try this formula

Local StringVar strBill;

strBill := Mid({YourField},InStrRev({YourField},"$bill");
If Length(strBill) > 10 then
    strBill := "*****" & strBill;

strBill

Open in new window


mlmcc
0
 

Expert Comment

by:sj_porter
ID: 39678987
[Edited] I misread your question, so the answer I provided was slightly off.
0
 

Author Comment

by:IO_Dork
ID: 39679255
For record keeping...the mid() was missing the second ")" at the end....
Mid({YourField},InStrRev({YourField},"$bill"));


Local StringVar strBill;

strBill := Mid({YourField},InStrRev({YourField},"$bill");
If Length(strBill) > 10 then
    strBill := "*****" & strBill;

strBill
0
 

Author Closing Comment

by:IO_Dork
ID: 39679256
simple and direct solution. perfect!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Mask on Parameter CR2008 28 50
Cyrstal Reports formula to check IsNull 8 30
Crystal reports vb.net 2 39
free Crystal Report viewer 4 18
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now