Crystal Reports alphanumeric sort problem

Hi all-

I use CR 2008 and have a field called itemCode which is a string and has values like R1, R10, R2, R44.002, R300, R9 etc. The "R" is always present and always at the beginning.
I need to sort them numericaly so that the result is R1, R2, R10 etc.  Currently it sorts like R1, R10, R2 etc.  
I need a formula to truncate the "R" and cast it to a number and then I can sort on the formula value.

Any help would be greatly appreciated.
Thanks
chamlightAsked:
Who is Participating?
 
mlmccCommented:
Try this idea

Create a formula
Val(Mid({itemCodeField},2)

Another way so you can keep the R

Left({itemCodeField},1) & CStr(Val(Mid({itemCodeField},2), "00000")

mlmcc

0
 
chamlightAuthor Commented:
mlmcc

Wow, that was fast.  Thanks, I'll try them.  BTW can you see any problems I might have with the following?


toNumber (replace ({chaProducts.itemCode},"R","",1,1,1))
0
 
mlmccCommented:
That should work as well

mlmcc
0
 
chamlightAuthor Commented:
Thanks.  Your solution is more...elegant
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.