Truncate formula in CR 1

[Jeffcis]

Hi Tech-Tips,

I'm looking for a truncate formula in CR to show only the NUMERICAL part of the below customer field.

For instance, the following customer names have long text name:

Belair Carpet Choice SN1208
Bendigo Bulk Carpet Choice SN 1319
Bob Smith Carpet Choice SN1310
Brian Bennies Carpet Choice SN1365
Brights Carpet Choice SN 1207

In my report I need a truncate formula in CR to show only the NUMERICAL part
1208
1319
1222.2
1310
1365
1207

Appreciate any help

Kr,
Jeffcis

 

[Turkbear]

Hi,
All strings are, to Crystal, just arrays of characters, so by looping through each position and testing to see if it is numeric ( CR has a function for this I believe), you should be able to build a new string with just those that pass the IsNumeric? test - The decimal point may cause an issue unless you test for it as well..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CR85user]

Hi Jeffcis -

Where in the above example of raw data did the 1222.2 come into it?

The rest should be easy to test for including the decimal point but I just want to correlate the data you show against that which you want to see.

'J

 

[Madawc]

If the last four digits are always the number, you can use

Right({your.field}, 4)

If it can be more, but there is always an SN, then

Split({your.field}, "SN)[2]

This isn't really truncation, which involves cutting off the decimal, e.g. 1.9 becomes 1 under truncation, 2 under rounding.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[CR85user]

Another alternateive (Again if always prefixed with 'SN')

trim(extractstring({String.field},'SN',' '))

Should grab the number you need.

No difference to Madawcs approach, it is just interesting to see the different ways to acheive the same end.

'J

 

[Jeffcis]

Hi Madawc, my customer names are not consistent because there are customer names with just DOT (.) For example, 1241.5 so it is not always 4 digits at the end. So I cannot use your 1st formula. 2nd formula it returns a blank page, again it has not work with error "the match for this string is missing where "SN)[2] are highlighted in BLUD.


Hello CR8User, your formula works fine. Also, can you come up with formula to pickup the NUMERICAL value in some customer name where the customer name doesn't have SN included.

For instance, show only the NUMBERICAL value of the below customer names:

Brian Bennies Carpet Choice SN1365
Bright Carpet Choice 1207
Breach Carpet Choice 1207.5

End result,
1365
1207
1207.5

many thanks
Jeffcis

 

[lbass]

Try this:

stringvar array x := split({table.field}," ");
stringvar y := trim(x[ubound(x)]);
replace(y,"SN","")

-LB