cancel
Showing results for 
Search instead for 
Did you mean: 

read numbers only from text and numbers field

Former Member
0 Kudos

Hello,

i have field which contains text and numbers, i need to retrieve the numbers only from it.

example:

PlaceIndex

smr2003

smr2004

smr2005

i need to exctract placeindex by range.

exapmle:

{Place.PlaceIndex} in "2000" to "2999" by ignoring the text before numbers.

any idea?

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

If number is always last 4 characters you can use a crystal formula

tonumber(right({Place.PlaceIndex},4), 0,"")

This will fail if not always 4 characters

Ian

János_at_SAP
Advisor
Advisor
0 Kudos

Hi,

if you would like to receive only the numbers, i suggest to convert it on data side with a function, since this will be more powerful, than to convert on the report side, since report side conversion maybe slower than database side.

if the data lenght is fixed, than you can use the right function or substring function to get your number in a variable.

right({Place.PlaceIndex},4) 

which will cut the 4 chats from right, so

right('ASBC2009',4) will return 2009.

Now you can probe that the result is number or not. you can use the isnumeric function for this.

At the end the formula will look like

IsNumeric(right({Place.PlaceIndex},4)) and (toNumber(right({Place.PlaceIndex,4)) >=2000 and toNumber(right({Place.PlaceIndex},4)) < 2100)

Now you need to record this formula into the record selection formulas inthe designer. Variable processing goes from left to right, so if first condition evaulated as false, this rest of the formula will be not processed.

Regards,

János

abhilash_kumar
Active Contributor
0 Kudos

Hi,

Does the string always contain the text 'smr' before numbers?

Are you using this in a Record Selection Formula?

-Abhilash