Find $Value in String 1

[dunkyn]

I need to extract a $ value from a string. Sometimes the string does not have a $ value in it. How do I exclude these strings? I am getting an error message "Start position is less than 1 or not an integer.

Formula for @TestforValue (look for $ to find value): "$" in {sp.DESC}

Formula to display value:

If {@TestForValue} = true then

numbervar startpos1;
numbervar endpos1;
numbervar allchar1;
stringvar val1;

startpos1:=InStrRev ({sp.DESC},"$"
endpos1:=InStrRev ({sp.DESC},"." );
allchar1:=endpos1-startpos1+3;
val1:= mid({sp.DESC},startpos1,allchar1);


Also - I want to make the outcome a number. I am getting error messages when I use toNumber(giftval1)

TIA

 

[Naith]

Can you show us an example of the actual output of {sp.DESC}, and just print how you want your formula to display?

Naith

 

[dunkyn]

INTEL CORP COM 474 SHARES TO CHARITABLE FDN A/C #99999999 TAXPAYER ID: 04-9999999 MADE DECEMBER 3,2002 AT VALUE $9,731.22

or

TRANSFERRED TO PORTFOLIO 11111111 SSN# 111-11-1111 COMPLETION OF $1,500.00 GIFT TO xyz charity....

I just want to extract the $9,731.22 or the $1,500.00 and make it a number.

 

[Naith]

Like this?

stringvar := {yourfield};
xxx := Replace(xxx,',','');
Val(Mid(xxx,InStr(xxx,'$')+1));

Make the formula lead with a currency symbol, by using Format/Currency Symbol.

Naith

 

[Naith]

stringvar := {yourfield};
xxx := Replace(xxx,',','');

should be

stringvar xxx := replace({yourfield},',','');

Naith

 

[dunkyn]

Naith, No luck. I get text, not a number ?

How does the replace function extract just the value following the $ sign?

 

[lbass]

You could try the following and then format it as currency:

tonumber(mid({sp.desc},instr({sp.desc},"$&quot,10))

The "10" could be "9", but I allowed for numbers in the tens of thousands.

-LB

 

[dunkyn]

Naith,

Appreciate your help.

I have been successful in extracting the value, when there is a value in the explanation text. However, my dilemma is what to do if no "$" appears, and excluding these. I am getting error messages because of this issue.

Any thoughts out there on a work around?

 

[Naith]

Give this a whirl:

StringVar xxx := Replace({YourField},',','');

If InStr(xxx,'$') > 0
Then Val(Mid(xxx,InStr(xxx,'$')+1))
Else 0;

Naith

 

[dunkyn]

perfect! thank you!!