Help with extracting data from field

[#1]

Hi all,

I have a database field called message that contains the following type of descriptive information below.  I loaded it into QlikView, but I only need the text that says Reason: and the text after that word.  The rest of the data is not relevant.  As you can see the length is variable so you cannot precisely extract it. Any ideas how to do this?  Thanks.

MessageTable

Order 28315349: Vendor assignment to vendor 47541 was deleted by user SUNIS.  Reason: qa

Order 28315349: Vendor assignment to vendor 144775 was deleted by user SUNIS.  Reason: rels

Order 28315349: Vendor assignment to vendor 66728 was deleted by user SUNIS.  Reason: resd

[Anonymous]

Does the below give you food for thought ?

     = mid (  'User SUNIS.  Reason: qa', index('User SUNIS.  Reason: qa', 'Reason') )

You can text it is a Text Box, and then replace 'User SUNIS.  Reason: qa' with your field name.

[maxgro · Accepted solution]

MessageTable:

load * inline [

field

Order 28315349: Vendor assignment to vendor 47541 was deleted by user SUNIS.  Reason: qa

Order 28315349: Vendor assignment to vendor 144775 was deleted by user SUNIS.  Reason: rels

Order 28315349: Vendor assignment to vendor 66728 was deleted by user SUNIS.  Reason: resd

];

expression

Right(field, len(field) -index(field, 'Reason') +1)

[#4]

Massimo, thank you again for your help.  Can you briefly explain how this works.

[maxgro]

in the image you can see the original field, then 3 columns:

c1      index(field, 'Reason')          position of 'Reason' in field

c2      len(field) -.c1 +1                length of the string I want as result

c3      rigth(field, c2)                    substring of field,  consisting of the last c2 characters of field

[#6]

That was very brilliant. Thanks.

[maxgro]

I think Bill answer is better, mid with 2 parameters

[#8]

Sorry, I didn't fully understand your answer.