How can I find the position number for a subsequent character occurence in a string? INSTR() will find the position of the first occurence in a string but I need to find out where the 4th space from the right is. I have a long string that I am bringing in and breaking apart into smaller pieces, see example below. I am using LSplit or RSplit with a space separator to get the left 3 parts and the right 4 parts. My problem is I have 2 fields left in the middle section ("111510 00 C/R MFC Pricing Cor") of the string and both fields may or may not have spaces in. In this example "111510 00 C/R MFC" should be one field and "Pricing Cor" should be the second field. I have determined that the max length of the second portion is 11 characters. Any suggestions on how to parse this apart?
03/14/11 03/29/11 7468799223 111510 00 C/R MFC Pricing Cor 0.00 9.48
Len() and Extract() might be your friends here. Firstly, you can isolate the middle portion of the string with:
Defining a calculated field called "Middle" based on this, we need to chop Middle up based on whether the fourth space " " appears within the last eleven characters - is that right?
Just to add to Olly's question ....
Are you able to determine any other rules about possible minimum and miximum widtsr of the text fields in the 'Middle' section?
Are there any other features of the data that would help to confirm how the string should be split? For instance It would be great if that section always started with a word or one of a list of words (or just strings of characters) that would not appear anywhere else in the string OR would always appear within a certain range of character positions within the string.
I'm assuming from your description that the first part of the field is not a fixed size as presented in the report.
The Monarch part should be quite easy once you know all the interpretation rules for the fields and have worked out which Monarch functions enable you to do what you need to do.