Extract numbers from a string 2

[cristianivanoff]

Hello all,

I would like to extract all numbers from a string. I have a field {TM_SE_sbreg.Reference} that can have these strings:

's-12344','r-se-123456','r-se-123455-3'

I need only the numbers that is: 12344, 123456, 1234553

I made the following formula:

if {TM_SE_sbreg.Reference} startswith "s" or {TM_SE_sbreg.Reference} startswith "r" then

if {TM_SE_sbreg.Reference} startswith "s" then tonumber(replace(mid({TM_SE_sbreg.Reference},3,len({TM_SE_sbreg.Reference})),"-",""))
else tonumber(replace(
mid({TM_SE_sbreg.Reference},6,len({TM_SE_sbreg.Reference})),"-","")

else 0

This work fine, but if I want to do a sort I get the following message: 'The string is non numberic'->tonumber(replace(mid({TM_SE_sbreg.Reference},3,len({TM_SE_sbreg.Reference})),"-",""))

Anybody have suggestion how to solve this?
Any help is appreciated.

Br
Cristian ivanoff

 

[1973kk]

i tried the followingformula u gave
if {Sheet1$.Reference} startswith "s" then
tonumber(replace(mid({Sheet1$.Reference},3,len({Sheet1$.Reference})),"-",""))
else if {Sheet1$.Reference} startswith "r" then tonumber(replace(mid({Sheet1$.Reference},6,len({Sheet1$.Reference})),"-",""))
else 0

it works fine. may be something is wrong with the data. may be they have strings other then r and s.

 

[cristianivanoff]

It works fine, but if I want to do a Sort I get that error message. Is there a function in CR that extracts numbers from strings? or something like that?

What could I do to ensure that I get only numbers from the string?

/cristian ivanoff

 

[BobSuruncle]

Try this:

Local StringVar f := {TM_SE_sbreg.Reference} ;
local Numbervar L := length(f) ;
Local Numbervar i ;
Local StringVar s ;
for i := 1 to L do
(
s := s & ( If IsNumeric(f[i]) then f[i] )
);
s
[/code

Bob Suruncle

 

[BobSuruncle]

Sorry, I forgot the final "]" in my reply.
This should be easier to read.

Local StringVar f := {TM_SE_sbreg.Reference} ;
local Numbervar L := length(f) ;
Local Numbervar i ;
Local StringVar s ;
for i := 1 to L do
(
s := s & ( If IsNumeric(f[i]) then f[i] )
);
s

Bob Suruncle

 

[1973kk]

The Val (str) function reads a string containing Numbers (example: an address, phone number, or social security number) and converts them to a decimal value. Val stops reading the string when it finds the first character in the string that it finds that it can not recognize as a number or as a space.

 

[cristianivanoff]

Yes! Thank you. The formula works fine!

The Val(str) function don't work because it stops reading when it finds the first character in the string. In my case that would be on the first character or first element in the string.

Br
Cristian

 

[cristianivanoff]

Hello all,

I still have a problem. The formula above works fine. This formula is now on a sub report and I want to link the main report to my sub report through a number field to this formula. I made a formula in the main report to convert the number field to a string (totext(number field)) but the linking doesn't work. Another way should be to convert the 's' variable in the formula above to a numbervar. But this doesn't work either. Any suggestions?

Br
Cristian Ivanoff

 

[lbass]

Change your formula to:

totext({table.number},0,"")

-LB

 

[cristianivanoff]

Amazing. It works perfectly but I don't know what the "" is for.

Thanks again...

Br
Cristian ivanoff

 

[lbass]

The "" removes the comma separater from numbers in the 1000's, and the 0 represents the number of decimals.

-LB