Remove first 3 positions in field 3

[techwriterAR]

I have a Crystal XI Report that uses a stored procedure to create a list of late shipments. The data is stored in MS SQL and the field is a character field. When I look at the field in my report, crystal calls it a string. What I need to do is remove the number, space and dash from the string field, but I cannot get TrimLeft, Trim, or Truncate to work.

The data looks like:
0 -Team Scheduling
2 -Inventory Counts
4 -Completed, External Delay
15-Non Plant Other

I need to return only:
Team Scheduling
Inventory Counts
Completed, External Delay
Non Plant Other

I have been working on this and searching for help for 3 hours without any luck and the report is due Wednesday by noon. I really hope someone can help me with this.

Thanks,
GJeffcoat

 

[elsenorjose]

Try:

Mid ({table.fieldname} ,InstrRev ({table.fieldname},"-") + 1 )

Good luck.

 

[lbass]

A little tweak:

Mid({table.fieldname} ,Instr({table.fieldname},"-")+1)

-LB

 

[elsenorjose]

Thanks for the fix LB

 

[techwriterAR]

I want to thank both of you, elsenorjose and lbass, for your help. The formula worked perfectly and I have my report ready for distribution. I really appreciate your help and the switfness of your reply. You really helped me out of a jam. I was wondering if I could impose on you again? I sort of understand Trim and Truncate, but I am not familiar with Mid. If you have the time, would one of you explain how this function works? I would really appreciate it!

Gratefully yours,
Gjeffcoat

 

[bdreed35]

Here is another way to do it, not necessarily any better than the ones above, just another way:

split({table.field},"-")[2]

~Brian

 

[CR85user]

The MID() formula works on the following basis:

Mid(string,startpoint,length)

For example:

Mid({table.string},3,6)

Would return the following output from the following intput

Input: 'HISDMSDJSDA'
Output: 'SDMSDJ'

The length value is optional - So you could just use the start position:

mid({table.string},4)

Input: '4 -Completed, External Delay'
Output: 'Completed, External Delay'

Input: '15-Non Plant Other'
Output: 'Non Plant Other'

Have a try with a sample string by creating a formula and using:

mid('ABCDEFGHIJKLMNOPQRSTUVWYZ123),x,z)

Change the x and z to different values to see how it is affected.

'J

 

[techwriterAR]

Thanks CR85user, I appreciate your explanation. I tried it out and it works! It was fun testing, it helped me see what you were talking about.

Gjeffcoat