Splitting a String

[Dodecahedron]

Hi,

I am using CR10.

I have a field which contains data in the following format
Mr & Mrs Smith
Mr Waddle
Ms King

I am looking to extract the title part i.e. Mr & Mrs, Mr, Ms in the example above

To get the surname I have used

stringVar array SplitInput := split({fieldname});
numberVar WordCount := ubound(SplitInput);

join(SplitInput[11 to WordCount])


To try and get the title I have used

stringVar array SplitInput := split({fmsaddr.fm_salute});
numberVar WordCount := ubound(SplitInput);

join(SplitInput[1 to WordCount - 1])

I get the error message the a subscript must be between 1 and the size of the array.

Can anybody help with the tile part

Thanks

 

[IdoMillet]

Is the "11" a typo or something you actually have in the formula?

If surname is the last word, then it should be:
SplitInput[ubound(SplitInput)]

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Dodecahedron]

sorry its a typo, to get the surname I used

join(SplitInput[-1 to WordCount])

I am now looking to extract the title i.e. Mr & Mrs, Mr etc

 

[IdoMillet]

Please clarify. What is the problem at this point? Did you try the expression I provided?

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

If you intend to just take everything up to the last word (which probably won't always work, as with your current formula, there are 2 part last names that aren't hyphenated), try:

left({table.field,instrrev({table.field," ")-1)

-k

 

[GJParker]

If there is always a title in the string then simply use

Split({Table.Field},' '}[1]

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[IdoMillet]

Gary, that won't work because the title can be multiple words...

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

If the ampersand is used consistently for "and" then you could use the following to get the address:

if instr({table.field},"&") > 0 then
split({table.field}," ")[1 to 3] else
split({table.field}," ")[1]

-LB