CR XI- Extraction Formula Please

[CRXIuser2005]

I need help.

My field may have either:

PRIOR FILE NUMBER 1234-N1234

OR

PRIOR FILE NUMBER 1234-N-1234

I need an extraction formula that will extract the number and place it in the following format:

1234-N1234

I did a RIGHT({fieldname},10}) but the SECOND DASH is cause it to be inconsisten.

THANK YOU FOR YOUR HELP...

Thank you.

MV

"A man is only as happy, as he makes up his mind to be....

 

[lbass]

Try this:

replace(trim(right({table.string},11)),"-N-","-N")

-LB

 

[synapsevampire]

Are you saying that the middle value is always N, or always an alpha, or?

Please try to be specific, onviously we can hardcode around that one field, so state specifically what the offending formats are.

Try this:

stringvar MyField := {table.field};
stringvar MyString:="";
Numbervar X;
for x := 1 to len(MyField)
do(
if isnumeric(mid(MyField,X,1)) then
MyString := MyString + mid(MyField,X,1)
else
if mid(MyField,X,1) <> "-" then
MyString:= MyString + "-"+mid(MyField,X,1)
);
MyString

We might also use a split function here if the alpha is always the same, I didn't want to make assumptions, rather, I wanted to make sure it always worked.

-k