Stripping numbers from a line of text with a formula...

[ukleaf]

Hi all, hope you can help...

basically I would like to use a formula to pluck out amounts from a line of text, the problem is we have 1000's of these lines and after the number there is a dash! Crystal is looking at this as if the numbers were negative amounts but they aren't.

is there a way of trimming all the text away to leave just the numbers?

many thanks

 

[KingfisherINC]

What CR version?

What database?

Can you provide an example record? is the dash always in the same place? i.e. "123-abc

 

[ukleaf]

It's Crystal version 9, and the dash is always in the same place, for example.

test goes here 1234.56 - some more text here

because of th dash after the number (including the space) it's taking it as a negative number.

many thanks

 

[KingfisherINC]

There may be more efficient ways (like doing it on the database) but this should work.

Split(Rtrim(Left({Table_Name.Column_Name},Instr({Table_Name.Column_Name},"-") - 1)))[-1]

This throws away everything after the "-" then converts the string to an array and pops the last element.
(rap the whole thing in ToNumber() if needed).

FYI: This question should be posted on the CR forum.

 

[synapsevampire]

Try:

if instr({table.field},"-") > 0 then
val(left({table.field},instr({table.field},"-")-1)))
else
0

This will check for a numeric value before the dash, and if it's found, return it, otherwise zero.

-k