Extract number from string field in Crystal reports

[SandyKerivan]

Hi,
I am trying to extract a number (a quote number which is always five numbers long) out of a string field that is not in any consistent pattern.
For example, the string field could be "quote number 12345", "quote no. 12345", "approved quote 12345".
I see the function VAL, but that needs the number to be first. Does anyone know of any other functions that can extract the number?

Thanks,
Sandy

 

[bdreed35]

If it is always last in the sequence, then this could work for you:

local stringvar input := split({table.field}," ");
val(input[ubound(input)])

~Brian

 

[SandyKerivan]

hi Brian,
Thanks for the tip, I think the number is always last. I just tried it though and it is saying "This array must be subscripted. For example: Array " and it is highlighting the part of the formula that says :
split({SV00300.Service_Description}, " ")

I've never dealt with arrays before, so I am not sure what the error is telling me.
Thanks,
Sandy

 

[lbass]

I think Brian meant:

local stringvar [red]array[/red] input := split({table.field}," ");
val(input[ubound(input)])

-LB

 

[IdoMillet]

Looks to me like Brian's approach should work, but here's an alternative:

Right({table.field},5)

If needed, you can of course convert to a numeric data type.

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[SandyKerivan]

Hi,
Thanks for your help, I got it to work both ways. I am going with Ido's suggestion of right({table.field},5) becuase after I got Brian's idea to work with LB's fix, I noticed not everyone inputting info into the database is putting a space between the word "quote" and the number so it still wasn't pulling all the numbers I needed. using the "right" function seems to get the best results in this case-but I will keep Brian's idea in my head for future use!

thanks again!
-sandy

 

[raja2008]

how about val() function it will extract only the numbers from a string ,if iam not wrong.

 

[dgillz]

val() only works on the left side of the string.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[dgillz]

There is a handy function in Crystal 8.5 and newer (maybe even farther back, but this is the oldest I have) called strreverse(). The will reverse the string and return:

"54321 etouq devorppa"

Use the val() function on this to get 54321.

Use the totext() function on this to get "54321".

use the strreverse() function again to get "12345".

Finally use the val() function again to get the number.

So your final formula would be:

Val(Strreverse(ToText(Val(Strreverse({YourField})),0,"")))

Give that a shot and let us know what happens

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?