Looking for formula to...

[DjangMan]

I'm debating writing a UFL for this but if someone has this formula that would be easier.

I have string values of '100393A', '3002-32C' and '39A553D'. I need the first number out of that string - 100393, 3002, 39. The value in the field will always start with a number but I don't know where the number ends.

Anyone have that formula or are others interested in a UFL?

DjangMan

 

[reporting]

Try the Val function. Val does exactly what you want. Let's say that your text string is called CLIENT.Address and contains the following records:

123 Main Street
3457 87th Street
456 Avenue 123

Val ({CLIENT.Address}) will return the following:

123
345787
456

HTH,
John Marrett
Crystal Reports & Crystal Enterprise Trainer

http://www.e-reporting.ca

 

[alley211]

The Val function works great if you are dealing with things that don't have the negative symbol in them. You have
3002-32c and the val function would convert this to:

-300232

So I would try this instead:

If instr ({test.27ae878}, &quot;-&quot < 1
Then val({test.27ae878})
Else
val(Left ({test.27ae878}, instr ({test.27ae878}, &quot;-&quot - 1))

It uses for the val function for everything but the items that have what crystal considers to be the negative symbol.

I have the 2nd val statement just in case something has a negative symbol but also has text values too.

Hope this helps,

alley

 

[DjangMan]

Thanks to the both of you. Neither solution was 100% for what I needed so I added that functionality to my existing UDF dll.

The subtle difference is that my function will terminate when a non-numeric character is found. The Crystal function 'accepts' spaces which is didn't want. I could have created a loop to find the first space and truncate the string but there is a limit of 30000 loop iterations in a report which I could easily hit.

Thanks again!

DjangMan

 

[dgillz]

Djangman-

That is cool. Would you care to email me the dll? I would like it for my aresenal.

dgilsdorf@altavista.com
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

You can also use the replace function to clean out characters like dashes (or spaces). You can either stop at the first dash, or ignore the dashes and take all of the numbers.

To stop at the dash I use:
Val( Replace ( {string} , '-' , 'x' ) )


To ignore the dashes I use:
Val( Replace ( {string} , '-' , '' ) )

You can even NEST the replaces to clean multiple characters. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com