Extracting specific text from text field

[WilmDel]

I am a new member and hoping that someone can help me...

I am trying to extract specific text from a large text field that may or may not contain the text I am looking for. I want to extract the text that follows the following string "Purpose:" Purpose can show up at any location within the text string. I have created a formula in Excel that works, but I have not had any luck in Business Objects.

Here is an example of the excel formula...

=MID(cell address,SEARCH("purpose",cell address,1),200)

Thanks!

 

[AndyMc]

Wilmdel

If you want to include the word "purpose", take out "+7" below:

numbervar pos := instr(ucase({table.field}),"PURPOSE");
if pos = 0 then
""
else
MID({table.field},pos+8)

Andy

 

[WilmDel]

Thank you Andy, unfortunately, I am using Business Objects Web Intelligence XI Release 2, and instr, ucase, and mid are not available functions that I am aware of....

 

[AndyMc]

Sorry about that, you might want to try:

http://www.tek-tips.com/threadminder.cfm?pid=393

 

[WilmDel]

Thanks for your assistance. I will give it a try...

 

[lauriesamh]

Hi, I'm using CR9 and I'm trying to pull the names of a people out of a large text field. The people are referenced in quotes. Example: The order is for (Sam Smith) on the date of 1/1/2001. Is there an easy way to do this?

So far I've tried this formula:
@Person
whileprintingrecords;
trim(split((split({tablename.TEXT},"(")[2]),")")[1])

I get the error: "A Subscript must be between 1 and the size of the array

 

[lbass]

If there is only one person identified in the field per record, then you could use:

extractstring({table.string},"(",")")

-LB