asked on

extract text at end of field

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

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sj_porter

[Edited] I misread your question, so the answer I provided was slightly off.

IO_Dork

ASKER

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

IO_Dork

ASKER

simple and direct solution. perfect!