Just change the third parameter:
SubField(A-name,' ',6) - this will get the 6th bit of the string when broken up by spaces.
Bear in mind that NRx Share values and TRx Volume values will all be mixed in together by this method. Maybe add another field so you'll have:
SubField(A-name,' ',5) AS TxType //this will give TRx or NRx
,SubField(A-name,' ',6) AS TxValue //this will give the Share or Volume number
Hope this helps,
thanks for reply,
My problem is that, the data is not having fixed no. of words.
it differs for every row like
market name1 product name1 TRx Volume
market name2 product name2 market name2 product name1 TRx Share
In this case, the subfield(field,'5') will give Volume but in second data, it will give name2.
so that is the problem i cannot use subfield.
Is there any function to match data like Start from TRx or NRx and take till end of that like Volume or Share?